{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "b7c4f05b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import DataFrame\n",
    "\n",
    "# from SQLserverconnector import SQLserverconnector\n",
    "import re\n",
    "import warnings\n",
    "\n",
    "\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "class SQLserver_connector():\n",
    "    def __init__(self,host,port,user,password,database):\n",
    "        self.conn = psycopg2.connect(user=user,\n",
    "                              password=password,\n",
    "                              host=host,\n",
    "                              port=port,\n",
    "                              database=database)\n",
    "        self.connInfo = [user,password,host,port,database]\n",
    "    def SQL_execute(self,sql):\n",
    "        try:\n",
    "            with self.conn.cursor() as cur:\n",
    "                cur.execute(sql)\n",
    "            self.conn.commit()\n",
    "        except Exception:\n",
    "            self.conn.rollback()\n",
    "            raise\n",
    "    def query(self, sql, fetchAll):\n",
    "        try:\n",
    "            with self.conn.cursor() as cur:\n",
    "                n = cur.execute(sql)\n",
    "                feilds = tuple(col[0] for col in cur.description)\n",
    "                if fetchAll:\n",
    "                    yield pd.DataFrame(list(cur.fetchall()), columns=feilds)\n",
    "                else:\n",
    "                    for _ in range(n):\n",
    "                        data = cur.fetchmany(0)\n",
    "                        yield pd.Series(data[0], index=feilds)\n",
    "        except:\n",
    "            self.conn.rollback()\n",
    "            raise\n",
    "            \n",
    "    def fetchOne(self, sql):\n",
    "        return self.query(sql, False)\n",
    "    def fetchAll(self, sql):\n",
    "        return next(self.query(sql, True))\n",
    "    def close(self):\n",
    "        self.conn.close()\n",
    "        \n",
    "    def dataToSql(self,data,targetTable):\n",
    "        try:\n",
    "            connInfo = 'postgresql+psycopg2://{0}:{1}@{2}:{3}/{4}'.format(\n",
    "                self.connInfo[0],\n",
    "                self.connInfo[1],\n",
    "                self.connInfo[2],\n",
    "                self.connInfo[3],\n",
    "                self.connInfo[4])\n",
    "            engine = create_engine(connInfo)\n",
    "            # # # 将数据写入sqlserver数据库中\n",
    "            data.to_sql(targetTable, engine, if_exists='append', index=False)\n",
    "        except:\n",
    "            self.conn.rollback()\n",
    "            raise\n",
    "        \n",
    "    def fetchOneList(self,sql):\n",
    "        with self.conn.cursor() as cur:\n",
    "            cur.execute(sql)\n",
    "            record = cur.fetchone()\n",
    "        return list(record)\n",
    "    \n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "DBlinks = {\n",
    "'mallbrainMain':\n",
    "    {'user':\"dqchina\",\n",
    "    'password':\"qspfjFYwbuf4yBkQ\",\n",
    "    'host':'pgm-uf69lit2h2hnhm34wo.pg.rds.aliyuncs.com',\n",
    "    'port':'3433',\n",
    "    'database':\"mallbrain\"},\n",
    "'sampleDB':\n",
    "    {'user':\"zsx_superuser\",\n",
    "    'password':\"zsx8182006\",\n",
    "    'host':'pgm-uf69lit2h2hnhm34wo.pg.rds.aliyuncs.com',\n",
    "    'port':'3433',\n",
    "    'database':\"sampledb_out\"},\n",
    "'normalenvDB':\n",
    "    {'user':\"zsx_superuser\",\n",
    "    'password':\"zsx8182006\",\n",
    "    'host':'pgm-uf69lit2h2hnhm34wo.pg.rds.aliyuncs.com',\n",
    "    'port':'3433',\n",
    "    'database':\"servedbforpeng\"},\n",
    "}\n",
    "\n",
    "myconnMallBrain = SQLserver_connector(DBlinks['mallbrainMain']['host'],\n",
    "                             DBlinks['mallbrainMain']['port'],\n",
    "                             DBlinks['mallbrainMain']['user'],\n",
    "                             DBlinks['mallbrainMain']['password'],\n",
    "                             DBlinks['mallbrainMain']['database'])\n",
    "\n",
    "myconnSample = SQLserver_connector(DBlinks['sampleDB']['host'],\n",
    "                             DBlinks['sampleDB']['port'],\n",
    "                             DBlinks['sampleDB']['user'],\n",
    "                             DBlinks['sampleDB']['password'],\n",
    "                             DBlinks['sampleDB']['database'])\n",
    "\n",
    "myconnNormalenv = SQLserver_connector(DBlinks['normalenvDB']['host'],\n",
    "                             DBlinks['normalenvDB']['port'],\n",
    "                             DBlinks['normalenvDB']['user'],\n",
    "                             DBlinks['normalenvDB']['password'],\n",
    "                             DBlinks['normalenvDB']['database'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "50b3101f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 常规参数\n",
    "last_ds='20230501'  # 参考月份\n",
    "new_ds='20230601'   #目标月份\n",
    "url = r'D:\\sunx\\资料\\小鹏汽车门店更新\\小鹏汽车门店更新\\新能源官网20230601'"
   ]
  },
  {
   "cell_type": "raw",
   "id": "bd695346",
   "metadata": {},
   "source": [
    "# 提取当月的门店（################更改提取的时间###############）  # ！！！！！！！！！！！！修改月份！！！！！！！！！！！！\n",
    "sql_xpcurrent = '''\n",
    "select *\n",
    "from ads_city_car_ori\n",
    "where update='20230401'   \n",
    "'''\n",
    "xpcurrent = myconnNormalenv.fetchAll(sql_xpcurrent)\n",
    "xpcurrent.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "cd9fe6a9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10765, 21)"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xpcurrent = pd.read_excel(url+'\\原始底表.xlsx',header = [0])\n",
    "xpcurrent = xpcurrent[['brand', 'gw_id', 'guanwangid', 'gw_type', 'isjiaofu', 'address', 'gd_lng', 'gd_lat','gd_mark',\n",
    "                       'name','openstatus','openhour1', 'openhour2','openhour3', 'district', 'is_open', 'type_new', \n",
    "                       'city_name', 'gw_name','update', 'id']]\n",
    "xpcurrent.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "5507055d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "需要对比分析的营业中的门店数量9561\n"
     ]
    }
   ],
   "source": [
    "typeDict = ['体验店', '服务中心|交付中心', '服务中心','4s店|交付中心', '体验店|交付中心', '4s店', '交付中心','第三方售后', \n",
    "            '展厅', '快闪店','展厅|交付中心','展厅|临时', '卫星店', '服务中心|临时', '交付中心|临时','体验店|交付中心|临时', \n",
    "            '第三方售后|临时']\n",
    "resAllmertype = xpcurrent.type_new.unique()\n",
    "\n",
    "# 新增type，需要\n",
    "typeadd = [i for i in resAllmertype if i not in typeDict]\n",
    "if len(typeadd)>0:\n",
    "    print('门店类型多出来了error！！！！！！！！typeadd！！！！')\n",
    "\n",
    "# 只分析状态为营业中且类型在list里面（可能要更新）的数据\n",
    "# 营业中的门店\n",
    "xpcurrentopen_ = xpcurrent[(xpcurrent.is_open.isin(['营业中']))\n",
    "                          &(xpcurrent.type_new.isin(['服务中心', '服务中心|交付中心', '体验店', '4s店|交付中心',\n",
    "                                                    '4s店','体验店|交付中心', '交付中心', '第三方售后','展厅|交付中心', '卫星店',\n",
    "                                                    '体验店|交付中心|临时', '第三方售后|临时']))]\n",
    "# 待开业的门店 或者 展厅临时店  --不做分析\n",
    "xpcurrentsoon = xpcurrent[(xpcurrent.is_open=='待开业')\n",
    "                          |(xpcurrent.type_new.isin(['展厅', '快闪店','展厅|临时', '服务中心|临时', '交付中心|临时']))]\n",
    "\n",
    "if xpcurrentopen_.shape[0]+xpcurrentsoon.shape[0]==xpcurrent.shape[0]:\n",
    "    print('需要对比分析的营业中的门店数量%s'%xpcurrentopen_.shape[0])\n",
    "else:\n",
    "    print('数据拆分有误error!!!!!!!!!!!!!!!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d7f70438",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "typeadd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1286fd81",
   "metadata": {},
   "source": [
    "### 官网门店类型合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "e20f8f32",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9561, 14)"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## 官网id替换\n",
    "src_guanwangidchange = r'D:\\sunx\\资料\\小鹏汽车门店更新\\小鹏汽车门店更新\\官网id替换.xlsx'\n",
    "guanwangidchange = pd.read_excel(src_guanwangidchange)\n",
    "guanwangidchange['guanwangid'] = guanwangidchange['guanwangid'].astype(str)\n",
    "guanwangidchange['guanwangid_change'] = guanwangidchange['guanwangid_change'].astype(str)\n",
    "xpcurrentopen_['guanwangid'] = xpcurrentopen_['guanwangid'].astype(str)\n",
    "\n",
    "xpcurrentopen = pd.merge(xpcurrentopen_,guanwangidchange,on=['brand','guanwangid'],how='left')\n",
    "\n",
    "# 把替换的官网叠加到guanwangid列\n",
    "xpcurrentopen = xpcurrentopen.reset_index(drop=True)\n",
    "xpcurrentopen.loc[xpcurrentopen[~xpcurrentopen.guanwangid_change.isnull()].index,'guanwangid_old']=xpcurrentopen['guanwangid']\n",
    "xpcurrentopen.loc[xpcurrentopen[~xpcurrentopen.guanwangid_change.isnull()].index,'guanwangid']=xpcurrentopen['guanwangid_change']\n",
    "\n",
    "if xpcurrentopen_.shape[0]!=xpcurrentopen.shape[0]:\n",
    "    print('官网替换有误error！！！！！！！！！！！！！') \n",
    "\n",
    "# 蔚来门店\n",
    "xpcurrentopen['name_old'] = xpcurrentopen['name']\n",
    "xpcurrentopen['name'] = xpcurrentopen['name'].replace({\n",
    "    '苏州张家港楷德':'张家港杨舍',\n",
    "    '石家庄裕华':'石家庄西美',\n",
    "    '烟台经开':'蔚来空间烟台金沙滩',\n",
    "    '徐州三环东':'蔚来空间徐州蔚德客',\n",
    "    '惠州惠城':'蔚来空间惠州惠南',\n",
    "    '长春永顺':'长春净月',\n",
    "    '常德武陵':'蔚来空间常德龙港路',\n",
    "    '北京亦庄交付':'蔚来空间北京亦庄',\n",
    "    '北京后沙峪':'蔚来空间北京后沙峪',\n",
    "    '上海北松公路':'蔚来空间上海北松公路',\n",
    "    '成都机场路':'蔚来空间成都高新机场路',\n",
    "    '合肥包河':'蔚来空间合肥包河',\n",
    "    '郑州文鼎':'蔚来空间郑州文鼎', \n",
    "    '重庆回兴':'蔚来空间重庆回兴',\n",
    "    '东莞新投':'蔚来空间东莞寮步',\n",
    "    '台州路南':\"蔚来中心台州永宁\",\n",
    "    '绍兴越城':'蔚来空间绍兴袍江汽车城',    \n",
    "    '长春净月':'蔚来空间长春净月赛特奥莱' ,\n",
    "    '杭州蔚博':'杭州华立科技园',\n",
    "    '保定莲池':'保定中冀'\n",
    "\n",
    "\n",
    "})\n",
    "\n",
    "xpcurrentopen = xpcurrentopen[['id', 'brand','guanwangid', 'address','gd_lng', 'gd_lat','name', 'is_open','type_new','city_name', 'update', \n",
    "                               'guanwangid_change','guanwangid_old','name_old']]\n",
    "\n",
    "xpcurrentopen.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d5f30b33",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>brand</th>\n",
       "      <th>guanwangid</th>\n",
       "      <th>address</th>\n",
       "      <th>gd_lng</th>\n",
       "      <th>gd_lat</th>\n",
       "      <th>name</th>\n",
       "      <th>is_open</th>\n",
       "      <th>type_new</th>\n",
       "      <th>city_name</th>\n",
       "      <th>update</th>\n",
       "      <th>guanwangid_change</th>\n",
       "      <th>guanwangid_old</th>\n",
       "      <th>name_old</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>57370</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>nan</td>\n",
       "      <td>北京亦庄经济技术开发区中和街16号3幢1003单元</td>\n",
       "      <td>116.518029</td>\n",
       "      <td>39.800425</td>\n",
       "      <td>北京亦庄</td>\n",
       "      <td>营业中</td>\n",
       "      <td>服务中心</td>\n",
       "      <td>北京市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>北京亦庄</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>57371</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>nan</td>\n",
       "      <td>北京市朝阳区崔各庄乡东辛店339号</td>\n",
       "      <td>116.506254</td>\n",
       "      <td>40.007125</td>\n",
       "      <td>北京五元桥</td>\n",
       "      <td>营业中</td>\n",
       "      <td>服务中心</td>\n",
       "      <td>北京市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>北京五元桥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>57372</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>nan</td>\n",
       "      <td>北京市石景山区永引渠北路临9号</td>\n",
       "      <td>116.214880</td>\n",
       "      <td>39.941191</td>\n",
       "      <td>北京杏石口</td>\n",
       "      <td>营业中</td>\n",
       "      <td>服务中心</td>\n",
       "      <td>北京市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>北京杏石口</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>57373</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>nan</td>\n",
       "      <td>北京市朝阳区金盏乡杨树岗村745号院1号楼-07室</td>\n",
       "      <td>116.581419</td>\n",
       "      <td>40.006330</td>\n",
       "      <td>北京金盏</td>\n",
       "      <td>营业中</td>\n",
       "      <td>服务中心</td>\n",
       "      <td>北京市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>北京金盏</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>57374</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>nan</td>\n",
       "      <td>北京市朝阳区北四环东路39号</td>\n",
       "      <td>116.453776</td>\n",
       "      <td>39.982968</td>\n",
       "      <td>北京太阳宫</td>\n",
       "      <td>营业中</td>\n",
       "      <td>服务中心</td>\n",
       "      <td>北京市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>北京太阳宫</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9556</th>\n",
       "      <td>68130</td>\n",
       "      <td>高合</td>\n",
       "      <td>205</td>\n",
       "      <td>山东省东营市东营区西四路920号</td>\n",
       "      <td>118.484202</td>\n",
       "      <td>37.417963</td>\n",
       "      <td>东营凯通汽车销售服务有限公司</td>\n",
       "      <td>营业中</td>\n",
       "      <td>第三方售后</td>\n",
       "      <td>东营市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>东营凯通汽车销售服务有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9557</th>\n",
       "      <td>68131</td>\n",
       "      <td>高合</td>\n",
       "      <td>206</td>\n",
       "      <td>淄博嘉骋汽车贸易有限公司</td>\n",
       "      <td>118.040176</td>\n",
       "      <td>36.771496</td>\n",
       "      <td>淄博嘉骋汽车贸易有限公司</td>\n",
       "      <td>营业中</td>\n",
       "      <td>第三方售后</td>\n",
       "      <td>淄博市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>淄博嘉骋汽车贸易有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9558</th>\n",
       "      <td>68132</td>\n",
       "      <td>高合</td>\n",
       "      <td>208</td>\n",
       "      <td>甘肃省兰州市城关区均家滩371-2</td>\n",
       "      <td>103.885309</td>\n",
       "      <td>36.056738</td>\n",
       "      <td>兰州赛弛英菲尼迪汽车销售服务有限公司</td>\n",
       "      <td>营业中</td>\n",
       "      <td>第三方售后</td>\n",
       "      <td>兰州市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>兰州赛弛英菲尼迪汽车销售服务有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9559</th>\n",
       "      <td>68133</td>\n",
       "      <td>高合</td>\n",
       "      <td>209</td>\n",
       "      <td>宁夏回族自治区银川市德胜工业园区109线东侧1幢</td>\n",
       "      <td>106.319481</td>\n",
       "      <td>38.534004</td>\n",
       "      <td>宁夏汇德汽车销售服务有限公司</td>\n",
       "      <td>营业中</td>\n",
       "      <td>第三方售后</td>\n",
       "      <td>银川市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>宁夏汇德汽车销售服务有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9560</th>\n",
       "      <td>68134</td>\n",
       "      <td>高合</td>\n",
       "      <td>210</td>\n",
       "      <td>湖北省宜昌市西陵区伍家岗区桔乡路491号</td>\n",
       "      <td>111.335958</td>\n",
       "      <td>30.697803</td>\n",
       "      <td>宜昌昕晨汽车服务有限公司</td>\n",
       "      <td>营业中</td>\n",
       "      <td>第三方售后</td>\n",
       "      <td>宜昌市</td>\n",
       "      <td>20230601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>宜昌昕晨汽车服务有限公司</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9561 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         id brand guanwangid                    address      gd_lng  \\\n",
       "0     57370    蔚来        nan  北京亦庄经济技术开发区中和街16号3幢1003单元  116.518029   \n",
       "1     57371    蔚来        nan          北京市朝阳区崔各庄乡东辛店339号  116.506254   \n",
       "2     57372    蔚来        nan            北京市石景山区永引渠北路临9号  116.214880   \n",
       "3     57373    蔚来        nan  北京市朝阳区金盏乡杨树岗村745号院1号楼-07室  116.581419   \n",
       "4     57374    蔚来        nan             北京市朝阳区北四环东路39号  116.453776   \n",
       "...     ...   ...        ...                        ...         ...   \n",
       "9556  68130    高合        205           山东省东营市东营区西四路920号  118.484202   \n",
       "9557  68131    高合        206               淄博嘉骋汽车贸易有限公司  118.040176   \n",
       "9558  68132    高合        208          甘肃省兰州市城关区均家滩371-2  103.885309   \n",
       "9559  68133    高合        209   宁夏回族自治区银川市德胜工业园区109线东侧1幢  106.319481   \n",
       "9560  68134    高合        210       湖北省宜昌市西陵区伍家岗区桔乡路491号  111.335958   \n",
       "\n",
       "         gd_lat                name is_open type_new city_name    update  \\\n",
       "0     39.800425                北京亦庄     营业中     服务中心       北京市  20230601   \n",
       "1     40.007125               北京五元桥     营业中     服务中心       北京市  20230601   \n",
       "2     39.941191               北京杏石口     营业中     服务中心       北京市  20230601   \n",
       "3     40.006330                北京金盏     营业中     服务中心       北京市  20230601   \n",
       "4     39.982968               北京太阳宫     营业中     服务中心       北京市  20230601   \n",
       "...         ...                 ...     ...      ...       ...       ...   \n",
       "9556  37.417963      东营凯通汽车销售服务有限公司     营业中    第三方售后       东营市  20230601   \n",
       "9557  36.771496        淄博嘉骋汽车贸易有限公司     营业中    第三方售后       淄博市  20230601   \n",
       "9558  36.056738  兰州赛弛英菲尼迪汽车销售服务有限公司     营业中    第三方售后       兰州市  20230601   \n",
       "9559  38.534004      宁夏汇德汽车销售服务有限公司     营业中    第三方售后       银川市  20230601   \n",
       "9560  30.697803        宜昌昕晨汽车服务有限公司     营业中    第三方售后       宜昌市  20230601   \n",
       "\n",
       "     guanwangid_change guanwangid_old            name_old  \n",
       "0                  NaN            NaN                北京亦庄  \n",
       "1                  NaN            NaN               北京五元桥  \n",
       "2                  NaN            NaN               北京杏石口  \n",
       "3                  NaN            NaN                北京金盏  \n",
       "4                  NaN            NaN               北京太阳宫  \n",
       "...                ...            ...                 ...  \n",
       "9556               NaN            NaN      东营凯通汽车销售服务有限公司  \n",
       "9557               NaN            NaN        淄博嘉骋汽车贸易有限公司  \n",
       "9558               NaN            NaN  兰州赛弛英菲尼迪汽车销售服务有限公司  \n",
       "9559               NaN            NaN      宁夏汇德汽车销售服务有限公司  \n",
       "9560               NaN            NaN        宜昌昕晨汽车服务有限公司  \n",
       "\n",
       "[9561 rows x 14 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xpcurrentopen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "83c6d5a3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9421, 14)"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 官网id和类型均重复的删除\n",
    "xpcurrentopennwl = xpcurrentopen[xpcurrentopen.brand!='蔚来'] \n",
    "xpcurrentopennwl_cf = xpcurrentopennwl[xpcurrentopennwl.duplicated(subset=[\"guanwangid\",\"type_new\",\"city_name\",\"brand\"], keep='first')]\n",
    "xpcurrentopen = xpcurrentopen[~xpcurrentopen.id.isin(xpcurrentopennwl_cf.id)]\n",
    "xpcurrentopen.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d4dcdb7",
   "metadata": {},
   "source": [
    "#### 除蔚来外，同一品牌、guanwangid的type_new进行合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "6e6ac397",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(351, 15)"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### 体验店和服务中心合并\n",
    "xpcurrentopennwl = xpcurrentopen[xpcurrentopen.brand!='蔚来']\n",
    "ty = xpcurrentopennwl[xpcurrentopennwl.type_new=='体验店']\n",
    "fw = xpcurrentopennwl[xpcurrentopennwl.type_new=='服务中心']\n",
    "\n",
    "hb4s = pd.merge(ty,fw,on=['brand','guanwangid'],how='inner')\n",
    "hb4s_oter = xpcurrentopennwl[(~xpcurrentopennwl.id.isin(hb4s.id_x)) & (~xpcurrentopennwl.id.isin(hb4s.id_y))]\n",
    "hb4s = hb4s[['id_x', 'brand', 'guanwangid','address_x','name_x','city_name_x','is_open_x', 'gd_lng_x', 'gd_lat_x',\n",
    "             'update_x','name_old_x','type_new_x','name_y','guanwangid_old_x','guanwangid_old_y']]\n",
    "hb4s.columns = ['id', 'brand', 'guanwangid','address','name_x','city_name','is_open', 'gd_lng', 'gd_lat',\n",
    "                'update','name_old','type_new','name_y','guanwangid_old_x','guanwangid_old_y']\n",
    "hb4s['type_new']='4s店'\n",
    "if hb4s.shape[0]>0:\n",
    "    hb4s['name'] =  hb4s.apply(lambda x: x['name_x'] if x['name_x']==x['name_y'] else x['name_x']+'|'+x['name_y'], axis=1)\n",
    "    hb4s = hb4s.reset_index(drop=True)\n",
    "    hb4s.loc[hb4s[(~hb4s.guanwangid_old_x.isnull())&(hb4s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb4s['guanwangid_old_x']\n",
    "    hb4s.loc[hb4s[(hb4s.guanwangid_old_x.isnull())&(~hb4s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb4s['guanwangid_old_y']\n",
    "    hb4s.loc[hb4s[(~hb4s.guanwangid_old_x.isnull())&(~hb4s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb4s['guanwangid_old_x']+';'+hb4s['guanwangid_old_y']\n",
    "    hb4s = hb4s[['id', 'brand', 'guanwangid', 'address', 'name_x', 'city_name','is_open', 'gd_lng', 'gd_lat', 'update', 'name_old', 'type_new',\n",
    "                 'name_y','name','guanwangid_old']]\n",
    "\n",
    "xpcurrentopennwl1 = pd.concat([hb4s,hb4s_oter])\n",
    "if xpcurrentopennwl1.shape[0] != xpcurrentopennwl.shape[0]-hb4s.shape[0]:\n",
    "    print('4s店合并失败') \n",
    "hb4s.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "b197d0ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, 15)"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### 体验店和交付中心合并\n",
    "ty2 = xpcurrentopennwl1[xpcurrentopennwl1.type_new=='体验店']\n",
    "jf2 = xpcurrentopennwl1[xpcurrentopennwl1.type_new=='交付中心']\n",
    "hbtj = pd.merge(ty2,jf2,on=['brand','guanwangid'],how='inner')\n",
    "hbtj_oter = xpcurrentopennwl1[(~xpcurrentopennwl1.id.isin(hbtj.id_x)) & (~xpcurrentopennwl1.id.isin(hbtj.id_y))]\n",
    "hbtj = hbtj[['id_x', 'brand', 'guanwangid','address_x','name_x','city_name_x','is_open_x', 'gd_lng_x', 'gd_lat_x',\n",
    "             'update_x','name_old_x','type_new_x','name_y','guanwangid_old_x','guanwangid_old_y']]\n",
    "hbtj.columns = ['id', 'brand', 'guanwangid','address','name_x','city_name','is_open', 'gd_lng', 'gd_lat',\n",
    "                'update','name_old','type_new','name_y','guanwangid_old_x','guanwangid_old_y']\n",
    "hbtj['type_new']='体验店|交付中心' \n",
    "if hbtj.shape[0]>0:\n",
    "    hbtj['name'] =  hbtj.apply(lambda x: x['name_x'] if x['name_x']==x['name_y'] else x['name_x']+'|'+x['name_y'], axis=1)\n",
    "    hbtj = hbtj.reset_index(drop=True)\n",
    "    hbtj.loc[hbtj[(~hbtj.guanwangid_old_x.isnull())&(hbtj.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hbtj['guanwangid_old_x']\n",
    "    hbtj.loc[hbtj[(hbtj.guanwangid_old_x.isnull())&(~hbtj.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hbtj['guanwangid_old_y']\n",
    "    hbtj.loc[hbtj[(~hbtj.guanwangid_old_x.isnull())&(~hbtj.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] =hbtj['guanwangid_old_x']+';'+hbtj['guanwangid_old_y']\n",
    "    hbtj = hbtj[['id', 'brand', 'guanwangid', 'address', 'name_x', 'city_name','is_open', 'gd_lng', 'gd_lat', 'update', 'name_old', 'type_new',\n",
    "                 'name_y','name','guanwangid_old']]\n",
    "xpcurrentopennwl2 = pd.concat([hbtj,hbtj_oter])\n",
    "if xpcurrentopennwl2.shape[0] != xpcurrentopennwl1.shape[0]-hbtj.shape[0]:\n",
    "    print('体验店|交付中心合并失败') \n",
    "hbtj.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "25c9f8b4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(23, 15)"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### 服务中心和交付中心合并\n",
    "ty3 = xpcurrentopennwl2[xpcurrentopennwl2.type_new=='服务中心']\n",
    "jf3 = xpcurrentopennwl2[xpcurrentopennwl2.type_new=='交付中心']\n",
    "hbfj = pd.merge(ty3,jf3,on=['brand','guanwangid'],how='inner')\n",
    "hbfj_oter = xpcurrentopennwl2[(~xpcurrentopennwl2.id.isin(hbfj.id_x)) & (~xpcurrentopennwl2.id.isin(hbfj.id_y))]\n",
    "hbfj = hbfj[['id_x', 'brand', 'guanwangid','address_x','name_x','city_name_x','is_open_x', 'gd_lng_x', 'gd_lat_x',\n",
    "             'update_x','name_old_x','type_new_x','name_y','guanwangid_old_x','guanwangid_old_y']]\n",
    "hbfj.columns = ['id', 'brand', 'guanwangid','address','name_x','city_name','is_open', 'gd_lng', 'gd_lat',\n",
    "                'update','name_old','type_new','name_y','guanwangid_old_x','guanwangid_old_y']\n",
    "hbfj['type_new']='服务中心|交付中心'\n",
    "if hbfj.shape[0]>0:\n",
    "    hbfj['name'] =  hbfj.apply(lambda x: x['name_x'] if x['name_x']==x['name_y'] else x['name_x']+'|'+x['name_y'], axis=1)\n",
    "    hbfj = hbfj.reset_index(drop=True)\n",
    "    hbfj.loc[hbfj[(~hbfj.guanwangid_old_x.isnull())&(hbfj.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hbfj['guanwangid_old_x']\n",
    "    hbfj.loc[hbfj[(hbfj.guanwangid_old_x.isnull())&(~hbfj.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hbfj['guanwangid_old_y']\n",
    "    hbfj.loc[hbfj[(~hbfj.guanwangid_old_x.isnull())&(~hbfj.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] =hbfj['guanwangid_old_x']+';'+hbfj['guanwangid_old_y']\n",
    "    hbfj = hbfj[['id', 'brand', 'guanwangid', 'address', 'name_x', 'city_name','is_open', 'gd_lng', 'gd_lat', 'update', 'name_old', 'type_new',\n",
    "                 'name_y','name','guanwangid_old']]\n",
    "xpcurrentopennwl3 = pd.concat([hbfj,hbfj_oter])\n",
    "if xpcurrentopennwl3.shape[0] != xpcurrentopennwl2.shape[0]-hbfj.shape[0]:\n",
    "    print('服务中心|交付中心合并失败') \n",
    "hbfj.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "f4fc02eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(33, 15)"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### 4s店和交付中心合并\n",
    "ty4 = xpcurrentopennwl3[xpcurrentopennwl3.type_new=='4s店']\n",
    "jf4 = xpcurrentopennwl3[xpcurrentopennwl3.type_new=='交付中心']\n",
    "hb4j = pd.merge(ty4,jf4,on=['brand','guanwangid'],how='inner')\n",
    "hb4j_oter = xpcurrentopennwl3[(~xpcurrentopennwl3.id.isin(hb4j.id_x)) & (~xpcurrentopennwl3.id.isin(hb4j.id_y))]\n",
    "hb4j = hb4j[['id_x', 'brand', 'guanwangid','address_x','name_x','city_name_x','is_open_x', 'gd_lng_x', 'gd_lat_x',\n",
    "             'update_x','name_old_x','type_new_x','name_y','guanwangid_old_x','guanwangid_old_y']]\n",
    "hb4j.columns = ['id', 'brand', 'guanwangid','address','name_x','city_name','is_open', 'gd_lng', 'gd_lat',\n",
    "                'update','name_old','type_new','name_y','guanwangid_old_x','guanwangid_old_y']\n",
    "hb4j['type_new']='4s店|交付中心'\n",
    "if hb4j.shape[0]>0:\n",
    "    hb4j['name'] =  hb4j.apply(lambda x: x['name_x'] if x['name_x']==x['name_y'] else x['name_x']+'|'+x['name_y'], axis=1)\n",
    "    hb4j = hb4j.reset_index(drop=True)\n",
    "    hb4j.loc[hb4j[(~hb4j.guanwangid_old_x.isnull())&(hb4j.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb4j['guanwangid_old_x']\n",
    "    hb4j.loc[hb4j[(hb4j.guanwangid_old_x.isnull())&(~hb4j.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb4j['guanwangid_old_y']\n",
    "    hb4j.loc[hb4j[(~hb4j.guanwangid_old_x.isnull())&(~hb4j.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] =hb4j['guanwangid_old_x']+';'+hb4j['guanwangid_old_y']\n",
    "    hb4j = hb4j[['id', 'brand', 'guanwangid', 'address', 'name_x', 'city_name','is_open', 'gd_lng', 'gd_lat', 'update', 'name_old', 'type_new',\n",
    "                 'name_y','name','guanwangid_old']]\n",
    "xpcurrentopennwl4 = pd.concat([hb4j,hb4j_oter])\n",
    "if xpcurrentopennwl4.shape[0] != xpcurrentopennwl3.shape[0]-hb4j.shape[0]:\n",
    "    print('4s店|交付中心合并失败') \n",
    "hb4j.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "5d8034ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 15)"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### 4s店和体验店合并\n",
    "ty5 = xpcurrentopennwl4[xpcurrentopennwl4.type_new=='体验店']\n",
    "jf5 = xpcurrentopennwl4[xpcurrentopennwl4.type_new=='4s店']\n",
    "hb5s = pd.merge(jf5,ty5,on=['brand','guanwangid'],how='inner')\n",
    "hb5s_oter = xpcurrentopennwl4[(~xpcurrentopennwl4.id.isin(hb5s.id_x)) & (~xpcurrentopennwl4.id.isin(hb5s.id_y))]\n",
    "hb5s = hb5s[['id_x', 'brand', 'guanwangid','address_x','name_x','city_name_x','is_open_x', 'gd_lng_x', 'gd_lat_x',\n",
    "             'update_x','name_old_x','type_new_x','name_y','guanwangid_old_x','guanwangid_old_y']]\n",
    "hb5s.columns = ['id', 'brand', 'guanwangid','address','name_x','city_name','is_open', 'gd_lng', 'gd_lat',\n",
    "                'update','name_old','type_new','name_y','guanwangid_old_x','guanwangid_old_y']\n",
    "hb5s['type_new']='4s店'\n",
    "if hb5s.shape[0]>0:\n",
    "    hb5s['name'] =  hb5s.apply(lambda x: x['name_x'] if x['name_x']==x['name_y'] else x['name_x']+'|'+x['name_y'], axis=1)\n",
    "    hb5s = hb5s.reset_index(drop=True)\n",
    "    hb5s.loc[hb5s[(~hb5s.guanwangid_old_x.isnull())&(hb5s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb5s['guanwangid_old_x']\n",
    "    hb5s.loc[hb5s[(hb5s.guanwangid_old_x.isnull())&(~hb5s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb5s['guanwangid_old_y']\n",
    "    hb5s.loc[hb5s[(~hb5s.guanwangid_old_x.isnull())&(~hb5s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] =hb5s['guanwangid_old_x']+';'+hb5s['guanwangid_old_y']\n",
    "    hb5s = hb5s[['id', 'brand', 'guanwangid', 'address', 'name_x', 'city_name','is_open', 'gd_lng', 'gd_lat', 'update', 'name_old', 'type_new',\n",
    "                 'name_y','name','guanwangid_old']]\n",
    "\n",
    "xpcurrentopennwl5 = pd.concat([hb5s,hb5s_oter])\n",
    "if xpcurrentopennwl5.shape[0] != xpcurrentopennwl4.shape[0]-hb5s.shape[0]:\n",
    "    print('4s店|体验店合并失败') \n",
    "hb5s.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "9b25f234",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 15)"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### 4s店和服务中心合并\n",
    "ty6 = xpcurrentopennwl5[xpcurrentopennwl5.type_new=='服务中心']\n",
    "jf6 = xpcurrentopennwl5[xpcurrentopennwl5.type_new=='4s店']\n",
    "hb6s = pd.merge(jf6,ty6,on=['brand','guanwangid'],how='inner')\n",
    "\n",
    "hb6s_oter = xpcurrentopennwl5[(~xpcurrentopennwl5.id.isin(hb6s.id_x)) & (~xpcurrentopennwl5.id.isin(hb6s.id_y))]\n",
    "hb6s = hb6s[['id_x', 'brand', 'guanwangid','address_x','name_x','city_name_x','is_open_x', 'gd_lng_x', 'gd_lat_x',\n",
    "             'update_x','name_old_x','type_new_x','name_y','guanwangid_old_x','guanwangid_old_y']]\n",
    "hb6s.columns = ['id', 'brand', 'guanwangid','address','name_x','city_name','is_open', 'gd_lng', 'gd_lat',\n",
    "                'update','name_old','type_new','name_y','guanwangid_old_x','guanwangid_old_y']\n",
    "hb6s['type_new']='4s店'\n",
    "if hb6s.shape[0]>0:\n",
    "    hb6s['name'] =  hb6s.apply(lambda x: x['name_x'] if x['name_x']==x['name_y'] else x['name_x']+'|'+x['name_y'], axis=1)\n",
    "    hb6s = hb6s.reset_index(drop=True)\n",
    "    hb6s.loc[hb6s[(~hb6s.guanwangid_old_x.isnull())&(hb6s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb6s['guanwangid_old_x']\n",
    "    hb6s.loc[hb6s[(hb6s.guanwangid_old_x.isnull())&(~hb6s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb6s['guanwangid_old_y']\n",
    "    hb6s.loc[hb6s[(~hb6s.guanwangid_old_x.isnull())&(~hb6s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] =hb6s['guanwangid_old_x']+';'+hb6s['guanwangid_old_y']\n",
    "    hb6s = hb6s[['id', 'brand', 'guanwangid', 'address', 'name_x', 'city_name','is_open', 'gd_lng', 'gd_lat', 'update', 'name_old', 'type_new',\n",
    "                 'name_y','name','guanwangid_old']]\n",
    "\n",
    "xpcurrentopennwl6 = pd.concat([hb6s,hb6s_oter])\n",
    "if xpcurrentopennwl6.shape[0] != xpcurrentopennwl5.shape[0]-hb6s.shape[0]:\n",
    "    print('4s店|服务中心合并失败') \n",
    "hb6s.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "3ecf8b8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, 15)"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### 体验店和服务中心|交付中心合并\n",
    "ty7 = xpcurrentopennwl6[xpcurrentopennwl6.type_new=='体验店']\n",
    "jf7 = xpcurrentopennwl6[xpcurrentopennwl6.type_new=='服务中心|交付中心']\n",
    "hb7s = pd.merge(ty7,jf7,on=['brand','guanwangid'],how='inner')\n",
    "hb7s_oter = xpcurrentopennwl6[(~xpcurrentopennwl6.id.isin(hb7s.id_x)) & (~xpcurrentopennwl6.id.isin(hb7s.id_y))]\n",
    "hb7s = hb7s[['id_x', 'brand', 'guanwangid','address_x','name_x','city_name_x','is_open_x', 'gd_lng_x', 'gd_lat_x',\n",
    "             'update_x','name_old_x','type_new_x','name_y','guanwangid_old_x','guanwangid_old_y']]\n",
    "hb7s.columns = ['id', 'brand', 'guanwangid','address','name_x','city_name','is_open', 'gd_lng', 'gd_lat',\n",
    "                'update','name_old','type_new','name_y','guanwangid_old_x','guanwangid_old_y']\n",
    "hb7s['type_new']='4s店|交付中心'\n",
    "if hb7s.shape[0]>0:\n",
    "    hb7s['name'] =  hb7s.apply(lambda x: x['name_x'] if x['name_x']==x['name_y'] else x['name_x']+'|'+x['name_y'], axis=1)\n",
    "    hb7s = hb7s.reset_index(drop=True)\n",
    "    hb7s.loc[hb7s[(~hb7s.guanwangid_old_x.isnull())&(hb7s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb7s['guanwangid_old_x']\n",
    "    hb7s.loc[hb7s[(hb7s.guanwangid_old_x.isnull())&(~hb7s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] = hb7s['guanwangid_old_y']\n",
    "    hb7s.loc[hb7s[(~hb7s.guanwangid_old_x.isnull())&(~hb7s.guanwangid_old_y.isnull())].index,\n",
    "             'guanwangid_old'] =hb7s['guanwangid_old_x']+';'+hb7s['guanwangid_old_y']\n",
    "    hb7s = hb7s[['id', 'brand', 'guanwangid', 'address', 'name_x', 'city_name','is_open', 'gd_lng', 'gd_lat', 'update', 'name_old', 'type_new',\n",
    "                 'name_y','name','guanwangid_old']]\n",
    "xpcurrentopennwl7 = pd.concat([hb7s,hb7s_oter])\n",
    "if xpcurrentopennwl7.shape[0] != xpcurrentopennwl6.shape[0]-hb7s.shape[0]:\n",
    "    print('4s店|交付中心合并失败') \n",
    "hb7s.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec1031d5",
   "metadata": {},
   "source": [
    "#### 蔚来单独处理后和前面的表做合并、输出新表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "dfd6b1cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "门店类型合并后门店数量8996\n"
     ]
    }
   ],
   "source": [
    "### 蔚来 体验店和服务中心合并\n",
    "xpcurrentopenwl = xpcurrentopen[xpcurrentopen.brand=='蔚来']\n",
    "xpcurrentopenwl.loc[:,'id'] = range(1,len(xpcurrentopenwl)+1)\n",
    "tywl = xpcurrentopenwl[xpcurrentopenwl.type_new=='体验店']\n",
    "fwwl = xpcurrentopenwl[xpcurrentopenwl.type_new=='服务中心']\n",
    "hb4swl = pd.merge(tywl,fwwl ,on=['name','brand'],how='inner')\n",
    "hb4swl_oter = xpcurrentopenwl[(~xpcurrentopenwl.id.isin(hb4swl.id_x)) & (~xpcurrentopenwl.id.isin(hb4swl.id_y))]\n",
    "hb4swl = hb4swl[['id_x', 'brand', 'guanwangid_x', 'address_x','name', 'city_name_x','is_open_x', 'gd_lng_x', 'gd_lat_x','update_x',\n",
    "                 'name_old_x','type_new_x']]\n",
    "hb4swl.columns = ['id', 'brand', 'guanwangid', 'address','name', 'city_name','is_open', 'gd_lng', 'gd_lat', 'update',\n",
    "                  'name_old','type_new']\n",
    "hb4swl['type_new']='4s店'\n",
    "\n",
    "if hb4swl.shape[0]>0:\n",
    "    hb4swl['name'] =  hb4swl.apply(lambda x: x['name'] if x['name_old']==x['name'] else x['name']+'|'+x['name_old'], axis=1)\n",
    "\n",
    "xpcurrentopenwlall = pd.concat([hb4swl,hb4swl_oter])\n",
    "if xpcurrentopenwlall.shape[0] != xpcurrentopenwl.shape[0]-hb4swl.shape[0]:\n",
    "    print('蔚来合并失败') \n",
    "    \n",
    "resAllnew = pd.concat([xpcurrentopennwl7,xpcurrentopenwlall])   \n",
    "print('门店类型合并后门店数量%s'%resAllnew.shape[0]) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02749fae",
   "metadata": {},
   "source": [
    "#### 对同一id不同name的数据进行处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "554052a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对合并的表格进行处理，name进行合并\n",
    "resAllnew = resAllnew.reset_index(drop=True)\n",
    "resAllnew['name_x'] = resAllnew['name_x'].astype(str)\n",
    "resAllnew['name_y'] = resAllnew['name_y'].astype(str)\n",
    "resAllnew.loc[resAllnew[(~resAllnew.guanwangid_change.isnull())&(resAllnew.guanwangid_old.isnull())].index,\n",
    "              'guanwangid_old'] = resAllnew['guanwangid_change']\n",
    "resAllnew.loc[resAllnew[(~resAllnew.guanwangid_change.isnull())&(~resAllnew.guanwangid_old.isnull())].index,\n",
    "             'guanwangid_old'] =resAllnew['guanwangid_change']+';'+resAllnew['guanwangid_old']\n",
    "resAllnew_ = resAllnew[resAllnew.name_x=='nan']\n",
    "resAllnew_oter = resAllnew[resAllnew.name_x!='nan']\n",
    "resAllnew_['name'] =  resAllnew_.apply(lambda x: x['name'] if x['name_x']==x['name_y'] else x['name_x']+'|'+x['name_y'], axis=1)\n",
    "resAllnew = pd.concat([resAllnew_,resAllnew_oter])\n",
    "resAllnew = resAllnew[['id', 'brand', 'guanwangid', 'address','city_name','is_open', 'gd_lng', 'gd_lat', 'update', 'name_old', 'type_new',\n",
    "                'name','guanwangid_old','guanwangid_change']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "974114b1",
   "metadata": {},
   "source": [
    "#### 标记重复数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "bf740631",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8996, 14)"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 官网id和类型均重复的删除\n",
    "resAllnewnwl = xpcurrentopen[xpcurrentopen.brand!='蔚来'] \n",
    "resAllnewnwl_cf = resAllnewnwl[resAllnewnwl.duplicated(subset=[\"guanwangid\",\"type_new\",\"city_name\",\"brand\"], keep='first')]\n",
    "resAllnew = resAllnew[~resAllnew.id.isin(resAllnewnwl_cf.id)]\n",
    "resAllnew.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "56cebffe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "名字重复是114条 namecf !!!!!!!!!!!!!!!!!!!!!!!\n",
      "地址重复是84条 addresscf !!!!!!!!!!!!!!!!!!!!!!!\n",
      "经纬度重复是109条 gdcf !!!!!!!!!!!!!!!!!!!!!!!\n",
      "官网id重复是8条 guanwangidcf !!!!!!!!!!!!!!!!!!!!!!!\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(8996, 15)"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "resAllnew['cfmark'] = ''\n",
    "resAllnew_ = resAllnew[~resAllnew.type_new.isin(['第三方售后','交付中心'])]\n",
    "resAllnew_sh = resAllnew[resAllnew.type_new.isin(['第三方售后','交付中心'])]\n",
    "\n",
    "# 官网名字重复的 \n",
    "namecf = []\n",
    "if resAllnew_[resAllnew_.duplicated(subset=[\"name\",\"brand\",\"city_name\"], keep=False)].shape[0]>0:\n",
    "    namecf = resAllnew_[resAllnew_.duplicated(subset=[\"name\",\"brand\",\"city_name\"], keep=False)].id\n",
    "    resAllnew_ = resAllnew_.reset_index(drop=True)\n",
    "    resAllnew_.loc[resAllnew_[resAllnew_.id.isin(namecf)].index,'cfmark']=resAllnew_['cfmark']+'+名字重复'\n",
    "    print('名字重复是%s条 namecf !!!!!!!!!!!!!!!!!!!!!!!'%namecf.shape[0])\n",
    "\n",
    "# 官网地址重复\n",
    "addresscf = []\n",
    "if resAllnew_[resAllnew_.duplicated(subset=[\"brand\",\"address\",\"city_name\"], keep=False)].shape[0]>0:\n",
    "    addresscf = resAllnew_[resAllnew_.duplicated(subset=[\"brand\",\"address\",\"city_name\"], keep=False)].id\n",
    "    resAllnew_ = resAllnew_.reset_index(drop=True)\n",
    "    resAllnew_.loc[resAllnew_[resAllnew_.id.isin(addresscf)].index,'cfmark']=resAllnew_['cfmark']+'+地址重复'\n",
    "    print('地址重复是%s条 addresscf !!!!!!!!!!!!!!!!!!!!!!!'%addresscf.shape[0])\n",
    "\n",
    "# 官网经纬度重复\n",
    "gdcf = []\n",
    "if resAllnew_[resAllnew_.duplicated(subset=[\"brand\",\"gd_lng\",\"gd_lat\",\"city_name\"], keep=False)].shape[0]>0:\n",
    "    gdcf = resAllnew_[resAllnew_.duplicated(subset=[\"brand\",\"gd_lng\",\"gd_lat\",\"city_name\"], keep=False)].id\n",
    "    resAllnew_ = resAllnew_.reset_index(drop=True)\n",
    "    resAllnew_.loc[resAllnew_[resAllnew_.id.isin(gdcf)].index,'cfmark']=resAllnew_['cfmark']+'+经纬度重复'\n",
    "    print('经纬度重复是%s条 gdcf !!!!!!!!!!!!!!!!!!!!!!!'%gdcf.shape[0])\n",
    "\n",
    "# 官网id去重\n",
    "guanwangidcf = []\n",
    "resAllnew_nwl = resAllnew_[resAllnew_.brand !='蔚来']\n",
    "if resAllnew_nwl[resAllnew_nwl.duplicated(subset=[\"guanwangid\",\"brand\"], keep=False)].shape[0]>0:\n",
    "    guanwangidcf = resAllnew_nwl[resAllnew_nwl.duplicated(subset=[\"guanwangid\",\"brand\"], keep=False)].id\n",
    "    resAllnew_ = resAllnew_.reset_index(drop=True)\n",
    "    resAllnew_.loc[resAllnew_[resAllnew_.id.isin(guanwangidcf)].index,'cfmark']=resAllnew_['cfmark']+'id重复'\n",
    "    print('官网id重复是%s条 guanwangidcf !!!!!!!!!!!!!!!!!!!!!!!'%guanwangidcf.shape[0])\n",
    "    \n",
    "    \n",
    "# 输出合并的结果表\n",
    "resAllnew_x = pd.concat([resAllnew_,resAllnew_sh])\n",
    "resAllnew_x.to_excel(r'C:\\Users\\NewUser\\Desktop\\门店类型合并结果表0614_1.xlsx')\n",
    "resAllnew_x.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bc59208",
   "metadata": {},
   "source": [
    "#### 人工清洗重复数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "6052ee61",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8952, 18)"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  人工清洗流程\n",
    "# 1 类型未合并的 但是名字、经纬度 或者 地址重复的门店,合并保留一条\n",
    "# 2 将guanwangid_old 分成两列，guanwangid_old1，和guanwangid_old2 并检查是否和之前的官网id存在重复\n",
    "# 3 经纬度重复的先看地址和名字是否重复，具体的经纬度坐标等后面和上月匹配后再核实是否重复\n",
    "\n",
    "# 导入新清洗的结果表\n",
    "src = r'C:\\Users\\NewUser\\Desktop\\门店类型合并结果表0614_1.xlsx'\n",
    "xpcurrent_ = pd.read_excel(src)\n",
    "xpcurrent_.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff44c64a",
   "metadata": {},
   "source": [
    "### 和上个月结果表进行对比"
   ]
  },
  {
   "cell_type": "raw",
   "id": "eaa2d968",
   "metadata": {},
   "source": [
    "更改月份参数！！！！！！"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "8b847275",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "本月8952 上个月7041\n"
     ]
    }
   ],
   "source": [
    "sql_xplast = '''\n",
    "select *\n",
    "from ads_city_car\n",
    "where update='20230501'\n",
    "and showtype<=4\n",
    "'''\n",
    "xplast_ = myconnNormalenv.fetchAll(sql_xplast)\n",
    "\n",
    "print('本月%s'%xpcurrent_.shape[0],'上个月%s'%xplast_.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "371d2b3d",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>city_name</th>\n",
       "      <th>name</th>\n",
       "      <th>brand</th>\n",
       "      <th>type</th>\n",
       "      <th>gd_lng</th>\n",
       "      <th>gd_lat</th>\n",
       "      <th>address</th>\n",
       "      <th>circle</th>\n",
       "      <th>circle_level</th>\n",
       "      <th>project</th>\n",
       "      <th>...</th>\n",
       "      <th>shoppingmallid</th>\n",
       "      <th>showtype</th>\n",
       "      <th>is_open</th>\n",
       "      <th>guanwangid</th>\n",
       "      <th>mark</th>\n",
       "      <th>district</th>\n",
       "      <th>guanwangtype</th>\n",
       "      <th>main_brand_list</th>\n",
       "      <th>telephone</th>\n",
       "      <th>idcarcity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>玉林市</td>\n",
       "      <td>比亚迪汽车王朝网玉林正霖4S店</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>4s店</td>\n",
       "      <td>110.191488</td>\n",
       "      <td>22.647609</td>\n",
       "      <td>广西壮族自治区玉林市经济开发区东区人民东路南侧100米（玉林市国防教育训练基地斜对面）</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>营业中</td>\n",
       "      <td>17482</td>\n",
       "      <td>None</td>\n",
       "      <td>玉州区</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>130.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中山市</td>\n",
       "      <td>中山港口SOHO新能源车城</td>\n",
       "      <td>极氪</td>\n",
       "      <td>体验店</td>\n",
       "      <td>113.389366</td>\n",
       "      <td>22.569238</td>\n",
       "      <td>中山市港口镇港口大道 SOHO 车城4#</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>营业中</td>\n",
       "      <td>Z323</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>东莞市</td>\n",
       "      <td>小鹏汽车东莞东益汽车园卫星店</td>\n",
       "      <td>小鹏</td>\n",
       "      <td>体验店</td>\n",
       "      <td>114.110000</td>\n",
       "      <td>22.840000</td>\n",
       "      <td>广东省东莞市塘厦镇东深路塘厦段30号东益智能装备新能源汽车集聚园2栋1-2层</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>营业中</td>\n",
       "      <td>1497</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>儋州市</td>\n",
       "      <td>小鹏汽车儋州夏日广场卫星店</td>\n",
       "      <td>小鹏</td>\n",
       "      <td>体验店</td>\n",
       "      <td>109.590000</td>\n",
       "      <td>19.530000</td>\n",
       "      <td>海南省儋州市兰洋北路111号1F-045B</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>营业中</td>\n",
       "      <td>1511</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>中山市</td>\n",
       "      <td>飞凡汽车 中山天奕星河COCO City轻体验空间</td>\n",
       "      <td>上汽R</td>\n",
       "      <td>体验店</td>\n",
       "      <td>113.421542</td>\n",
       "      <td>22.518803</td>\n",
       "      <td>广东省中山市东区天奕星河COCO City商场中庭</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>中山天奕星河COCO City</td>\n",
       "      <td>...</td>\n",
       "      <td>7592.0</td>\n",
       "      <td>1</td>\n",
       "      <td>营业中</td>\n",
       "      <td>23484</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7036</th>\n",
       "      <td>昌吉回族自治州</td>\n",
       "      <td>比亚迪汽车王朝网昌吉昊天翔4S店</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>4s店</td>\n",
       "      <td>87.240295</td>\n",
       "      <td>44.027257</td>\n",
       "      <td>新疆昌吉州昌吉市夹滩村宏东汽车城</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>营业中</td>\n",
       "      <td>15888</td>\n",
       "      <td>None</td>\n",
       "      <td>昌吉市</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7037</th>\n",
       "      <td>昌吉回族自治州</td>\n",
       "      <td>比亚迪汽车海洋网昌吉瑞迪4S店</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>4s店</td>\n",
       "      <td>87.242780</td>\n",
       "      <td>44.024727</td>\n",
       "      <td>新疆昌吉回族自治州昌吉市乌伊西路与中心路十字路口南侧远驰汽车城院内</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>营业中</td>\n",
       "      <td>17655</td>\n",
       "      <td>None</td>\n",
       "      <td>昌吉市</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7038</th>\n",
       "      <td>保定市</td>\n",
       "      <td>比亚迪汽车王朝网雄安申泽瑞迪4S店</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>4s店</td>\n",
       "      <td>116.130390</td>\n",
       "      <td>38.986458</td>\n",
       "      <td>河北省保定市雄县雄州镇崔村旅游路路北99号</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>营业中</td>\n",
       "      <td>16528</td>\n",
       "      <td>None</td>\n",
       "      <td>雄县</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7039</th>\n",
       "      <td>保定市</td>\n",
       "      <td>比亚迪汽车海洋网高碑店顶蓝4S店</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>4s店</td>\n",
       "      <td>115.909209</td>\n",
       "      <td>39.339467</td>\n",
       "      <td>河北省保定市高碑店市北城办事处新泰东路5号</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>营业中</td>\n",
       "      <td>15503</td>\n",
       "      <td>None</td>\n",
       "      <td>高碑店市</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7040</th>\n",
       "      <td>重庆市</td>\n",
       "      <td>比亚迪汽车海洋网重庆尚盈广耀4S店</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>4s店</td>\n",
       "      <td>106.235733</td>\n",
       "      <td>29.531008</td>\n",
       "      <td>重庆市璧山区壁泉街道铝山路1号（厂房）</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>营业中</td>\n",
       "      <td>15075</td>\n",
       "      <td>None</td>\n",
       "      <td>璧山区</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7041 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     city_name                       name brand type      gd_lng     gd_lat  \\\n",
       "0          玉林市            比亚迪汽车王朝网玉林正霖4S店   比亚迪  4s店  110.191488  22.647609   \n",
       "1          中山市              中山港口SOHO新能源车城    极氪  体验店  113.389366  22.569238   \n",
       "2          东莞市             小鹏汽车东莞东益汽车园卫星店    小鹏  体验店  114.110000  22.840000   \n",
       "3          儋州市              小鹏汽车儋州夏日广场卫星店    小鹏  体验店  109.590000  19.530000   \n",
       "4          中山市  飞凡汽车 中山天奕星河COCO City轻体验空间   上汽R  体验店  113.421542  22.518803   \n",
       "...        ...                        ...   ...  ...         ...        ...   \n",
       "7036   昌吉回族自治州           比亚迪汽车王朝网昌吉昊天翔4S店   比亚迪  4s店   87.240295  44.027257   \n",
       "7037   昌吉回族自治州            比亚迪汽车海洋网昌吉瑞迪4S店   比亚迪  4s店   87.242780  44.024727   \n",
       "7038       保定市          比亚迪汽车王朝网雄安申泽瑞迪4S店   比亚迪  4s店  116.130390  38.986458   \n",
       "7039       保定市           比亚迪汽车海洋网高碑店顶蓝4S店   比亚迪  4s店  115.909209  39.339467   \n",
       "7040       重庆市          比亚迪汽车海洋网重庆尚盈广耀4S店   比亚迪  4s店  106.235733  29.531008   \n",
       "\n",
       "                                          address circle circle_level  \\\n",
       "0     广西壮族自治区玉林市经济开发区东区人民东路南侧100米（玉林市国防教育训练基地斜对面）   None         None   \n",
       "1                            中山市港口镇港口大道 SOHO 车城4#   None         None   \n",
       "2          广东省东莞市塘厦镇东深路塘厦段30号东益智能装备新能源汽车集聚园2栋1-2层   None         None   \n",
       "3                           海南省儋州市兰洋北路111号1F-045B   None         None   \n",
       "4                       广东省中山市东区天奕星河COCO City商场中庭   None         None   \n",
       "...                                           ...    ...          ...   \n",
       "7036                             新疆昌吉州昌吉市夹滩村宏东汽车城   None         None   \n",
       "7037            新疆昌吉回族自治州昌吉市乌伊西路与中心路十字路口南侧远驰汽车城院内   None         None   \n",
       "7038                        河北省保定市雄县雄州镇崔村旅游路路北99号   None         None   \n",
       "7039                        河北省保定市高碑店市北城办事处新泰东路5号   None         None   \n",
       "7040                          重庆市璧山区壁泉街道铝山路1号（厂房）   None         None   \n",
       "\n",
       "              project  ... shoppingmallid showtype  is_open  guanwangid  mark  \\\n",
       "0                None  ...            NaN        2      营业中       17482  None   \n",
       "1                None  ...            NaN        1      营业中        Z323  None   \n",
       "2                None  ...            NaN        1      营业中        1497  None   \n",
       "3                None  ...            NaN        1      营业中        1511  None   \n",
       "4     中山天奕星河COCO City  ...         7592.0        1      营业中       23484  None   \n",
       "...               ...  ...            ...      ...      ...         ...   ...   \n",
       "7036             None  ...            NaN        2      营业中       15888  None   \n",
       "7037             None  ...            NaN        2      营业中       17655  None   \n",
       "7038             None  ...            NaN        2      营业中       16528  None   \n",
       "7039             None  ...            NaN        2      营业中       15503  None   \n",
       "7040             None  ...            NaN        2      营业中       15075  None   \n",
       "\n",
       "     district  guanwangtype  main_brand_list telephone idcarcity  \n",
       "0         玉州区          None             None      None     130.0  \n",
       "1        None          None             None      None       NaN  \n",
       "2        None          None             None      None       NaN  \n",
       "3        None          None             None      None       NaN  \n",
       "4        None          None             None      None       NaN  \n",
       "...       ...           ...              ...       ...       ...  \n",
       "7036      昌吉市          None             None      None       NaN  \n",
       "7037      昌吉市          None             None      None       NaN  \n",
       "7038       雄县          None             None      None       NaN  \n",
       "7039     高碑店市          None             None      None       NaN  \n",
       "7040      璧山区          None             None      None       NaN  \n",
       "\n",
       "[7041 rows x 26 columns]"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xplast_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "c1718674",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "营业中    6454\n",
       "新店      587\n",
       "Name: is_open, dtype: int64"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xplast_.is_open.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "0e50f97c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "本月8370 上月6457 对比后9210\n",
      "营业中     5629\n",
      "新店      2753\n",
      "暂停营业     828\n",
      "Name: status, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "###  提取上个月和本月的数据（剔除蔚来）\n",
    "current_nwl= xpcurrent_[xpcurrent_.brand != '蔚来']\n",
    "last_nwl = xplast_[xplast_.brand != '蔚来'] \n",
    "# current_nwl['guanwangid'] = current_nwl['guanwangid'].astype(float)\n",
    "# last_nwl['guanwangid'] = last_nwl['guanwangid'].astype(float)\n",
    "\n",
    "# 核查关联门店的匹配的条件（官网id、品牌名和类型）是否唯一（检核人工部分是否出错）\n",
    "cf1 = []\n",
    "cf2 = []\n",
    "if current_nwl[current_nwl.duplicated(subset=[\"guanwangid\",\"brand\"], keep=False)].shape[0]>0:\n",
    "    cf1 = current_nwl[current_nwl.duplicated(subset=[\"guanwangid\",\"brand\"], keep=False)]\n",
    "    print('本月匹配条件不唯一error!!!!!!!!!!!!!! cf1 !!!!!!!!!!!!!!')\n",
    "    \n",
    "if last_nwl[last_nwl.duplicated(subset=[\"guanwangid\",\"brand\",\"type\"], keep=False)].shape[0]>0:\n",
    "    cf2 = last_nwl[last_nwl.duplicated(subset=[\"guanwangid\",\"brand\",\"type\"], keep=False)]\n",
    "    print('上月匹配条件不唯一error!!!!!!!!!!!!!! !!!!cf2!!!!!!!!!!')\n",
    "    \n",
    "# 开始对比\n",
    "# guanwangid\n",
    "resAllmer1 = pd.merge(current_nwl,last_nwl,on=['guanwangid','brand'],how='outer')\n",
    "resAllmer1 = resAllmer1.reset_index(drop=True)\n",
    "\n",
    "# 全部打标为营业中\n",
    "resAllmer1['status']='营业中' \n",
    "# 本月没了就是暂停营业，上月没有，本月新增就是新店\n",
    "resAllmer1.loc[resAllmer1[resAllmer1['name_x'].isnull()].index,'status']='暂停营业'\n",
    "resAllmer1.loc[resAllmer1[resAllmer1['name_y'].isnull()].index,'status']='新店'\n",
    "\n",
    "# 提取新店\n",
    "resAllopennew1 =resAllmer1[resAllmer1.status.isin(['新店'])]\n",
    "\n",
    "resAllopennew1 = resAllopennew1[['id_x', 'brand','guanwangid','address_x','city_name_x','is_open_x','gd_lng_x', 'gd_lat_x','name_old',\n",
    "                                 'type_new','name_x','name_y', 'type','project','guanwangid_old1', 'guanwangid_old2',  'carbrandid',\n",
    "                                 'shoppingmallid','status']]\n",
    "\n",
    "resAllopennew1.columns = ['id', 'brand','guanwangid','address','city_name','is_open','gd_lng', 'gd_lat','name_old',\n",
    "                          'type_new','name', 'name_last', 'type','project','guanwangid_old1', 'guanwangid_old2','carbrandid', \n",
    "                          'shoppingmallid','status']\n",
    "\n",
    "# 提取营业中的店\n",
    "resAllopenneyy =resAllmer1[resAllmer1.status.isin(['营业中'])]\n",
    "resAllopenneyy = resAllopenneyy[['id_x', 'brand','guanwangid','address_x','city_name_x','is_open_x','gd_lng_y', 'gd_lat_y','name_old',\n",
    "                                 'type_new','name_x','name_y', 'type','project','guanwangid_old1', 'guanwangid_old2',  'carbrandid',\n",
    "                                 'shoppingmallid','status']]\n",
    "\n",
    "resAllopenneyy.columns = ['id', 'brand','guanwangid','address','city_name','is_open','gd_lng', 'gd_lat','name_old',\n",
    "                          'type_new','name', 'name_last', 'type','project','guanwangid_old1', 'guanwangid_old2','carbrandid', 'shoppingmallid','status']\n",
    "\n",
    "# 提取暂停营业的店\n",
    "resAllclose1 = resAllmer1[resAllmer1.status == '暂停营业']\n",
    "resAllclose1 = resAllclose1[[ 'brand', 'guanwangid','city_name_y', 'name_y', 'type', 'gd_lng_y','gd_lat_y', 'address_y',  'project',\n",
    "                             'carbrandid', 'id_y','shoppingmallid', 'status']]\n",
    "\n",
    "resAllclose1.columns = ['brand', 'guanwangid','city_name', 'name', 'type', 'gd_lng','gd_lat', 'address',  'project',\n",
    "                        'carbrandid', 'id','shoppingmallid', 'status']\n",
    "\n",
    "# 合并三个类型的店\n",
    "resAllmer = pd.concat([resAllopennew1,resAllopenneyy,resAllclose1])\n",
    "\n",
    "print('本月%s'%current_nwl.shape[0],'上月%s'%last_nwl.shape[0],'对比后%s'%resAllmer.shape[0])\n",
    "\n",
    "print(resAllmer.status.value_counts())\n",
    "\n",
    "# print('各个品牌门店数量是：%s'%resAllmer.groupby(['brand','status'])[\"brand\"].count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "cee3161d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "可替换的官网1：207 新店    207\n",
      "Name: status, dtype: int64\n",
      "替换1后门店数量：9210\n"
     ]
    }
   ],
   "source": [
    "# 检查替换的官网id是否存在\"\"\"营业中\"\"\"的店铺，如果有需更新到上面的表中\n",
    "# guanwangid_old1\n",
    "resAllmer_ = resAllmer[(resAllmer.status.isin(['营业中']))|(resAllmer.guanwangid_old1.isnull())]\n",
    "# 官网id发生了变更，导致分类误差\n",
    "current_nwl2_ = resAllmer[(resAllmer.status.isin(['暂停营业','新店']))&~(resAllmer.guanwangid_old1.isnull())]\n",
    "\n",
    "resAllmer2 = pd.merge(current_nwl2_,last_nwl,left_on=['guanwangid_old1','brand'],right_on=['guanwangid','brand'],how='left')\n",
    "resAllmer2 = resAllmer2 .reset_index(drop=True)\n",
    "resAllmer2['status']='营业中' \n",
    "resAllmer2.loc[resAllmer2[resAllmer2['name_x'].isnull()].index,'status']='暂停营业'\n",
    "resAllmer2.loc[resAllmer2[resAllmer2['name_y'].isnull()].index,'status']='新店'\n",
    "print('可替换的官网1：%s'%current_nwl2_.shape[0],resAllmer2.status.value_counts())\n",
    "\n",
    "resAllmer2_opennew =resAllmer2[resAllmer2.status.isin(['新店'])]\n",
    "resAllmer2_opennew = resAllmer2_opennew[['id_x', 'brand','guanwangid_x','address_x','city_name_x','is_open_x','gd_lng_x', 'gd_lat_x',\n",
    "                                         'name_old','type_new','name_x','guanwangid_old1', 'guanwangid_old2','name_y', 'type_y','project_y',\n",
    "                                         'carbrandid_y','shoppingmallid_y','status']]\n",
    "resAllmer2_opennew.columns = ['id', 'brand','guanwangid','address','city_name','is_open','gd_lng', 'gd_lat',\n",
    "                              'name_old','type_new','name','guanwangid_old1', 'guanwangid_old2','name_last', 'type','project',\n",
    "                              'carbrandid','shoppingmallid','status']\n",
    "\n",
    "\n",
    "resAllmer2_openyy =resAllmer2[resAllmer2.status.isin(['营业中'])]\n",
    "resAllmer2_openyy = resAllmer2_openyy[['id_x', 'brand','guanwangid_x','address_x','city_name_x','is_open_x','gd_lng_y', 'gd_lat_y',\n",
    "                                         'name_old','type_new','name_x','guanwangid_old1', 'guanwangid_old2','name_y', 'type_y','project_y',\n",
    "                                         'carbrandid_y','shoppingmallid_y','status']]\n",
    "resAllmer2_openyy.columns = ['id', 'brand','guanwangid','address','city_name','is_open','gd_lng', 'gd_lat',\n",
    "                              'name_old','type_new','name','guanwangid_old1', 'guanwangid_old2','name_last', 'type','project',\n",
    "                              'carbrandid','shoppingmallid','status']\n",
    "\n",
    "\n",
    "resAllmer2_close1 = resAllmer2[resAllmer2.status == '暂停营业']\n",
    "resAllmer2_close1 = resAllmer2_close1[[ 'brand', 'guanwangid_y','city_name_y', 'name_y', 'type_y', 'gd_lng_y','gd_lat_y', 'address_y',\n",
    "                                       'project_y','carbrandid_y', 'id_y','shoppingmallid_y', 'status']]\n",
    "resAllmer2_close1.columns = ['brand', 'guanwangid','city_name', 'name', 'type', 'gd_lng','gd_lat', 'address',  'project','carbrandid', \n",
    "                             'id','shoppingmallid', 'status']\n",
    "\n",
    "resAllmer_nwl = pd.concat([resAllmer_,resAllmer2_opennew,resAllmer2_openyy,resAllmer2_close1])\n",
    "print('替换1后门店数量：%s'%resAllmer_nwl.shape[0])\n",
    "\n",
    "# guanwangid_old2\n",
    "# resAllmer2_ = resAllmer[(resAllmer.status.isin(['营业中']))|(resAllmer.guanwangid_old2.isnull())]\n",
    "# current_nwl2_ = resAllmer[(resAllmer.status.isin(['暂停营业','新店']))&~(resAllmer.guanwangid_old2.isnull())]\n",
    "# resAllmer3 = pd.merge(current_nwl2_,last_nwl,left_on=['guanwangid_old2','brand'],right_on=['guanwangid','brand'],how='left')\n",
    "# resAllmer3 = resAllmer3 .reset_index(drop=True)\n",
    "# resAllmer3['status']='营业中' \n",
    "# resAllmer3.loc[resAllmer3[resAllmer3['name_x'].isnull()].index,'status']='暂停营业'\n",
    "# resAllmer3.loc[resAllmer3[resAllmer3['name_y'].isnull()].index,'status']='新店'\n",
    "# print('可替换的官网2：%s'%current_nwl2_.shape[0],resAllmer3.status.value_counts())\n",
    "\n",
    "# resAllmer3_opennew =resAllmer3[resAllmer3.status.isin(['新店'])]\n",
    "# resAllmer3_opennew = resAllmer3_opennew[['id_x', 'brand','guanwangid_x','address_x','city_name_x','is_open_x','gd_lng_x', 'gd_lat_x',\n",
    "#                                          'name_old','type_new','name_x','guanwangid_old1', 'guanwangid_old2','name_y', 'type_y','project_y',\n",
    "#                                          'carbrandid_y','shoppingmallid_y','status']]\n",
    "# resAllmer3_opennew.columns = ['id', 'brand','guanwangid','address','city_name','is_open','gd_lng', 'gd_lat',\n",
    "#                               'name_old','type_new','name','guanwangid_old1', 'guanwangid_old2','name_last', 'type','project',\n",
    "#                               'carbrandid','shoppingmallid','status']\n",
    "\n",
    "# resAllmer3_openyy =resAllmer3[resAllmer3.status.isin(['营业中'])]\n",
    "# resAllmer3_openyy = resAllmer3_openyy[['id_x', 'brand','guanwangid_x','address_x','city_name_x','is_open_x','gd_lng_y', 'gd_lat_y',\n",
    "#                                          'name_old','type_new','name_x','guanwangid_old1', 'guanwangid_old2','name_y', 'type_y','project_y',\n",
    "#                                          'carbrandid_y','shoppingmallid_y','status']]\n",
    "# resAllmer3_openyy.columns = ['id', 'brand','guanwangid','address','city_name','is_open','gd_lng', 'gd_lat',\n",
    "#                               'name_old','type_new','name','guanwangid_old1', 'guanwangid_old2','name_last', 'type','project',\n",
    "#                               'carbrandid','shoppingmallid','status']\n",
    "\n",
    "\n",
    "# resAllmer3_close1 = resAllmer3[resAllmer3.status == '暂停营业']\n",
    "# resAllmer3_close1 = resAllmer3_close1[[ 'brand', 'guanwangid_y','city_name_y', 'name_y', 'type_y', 'gd_lng_y','gd_lat_y', 'address_y',\n",
    "#                                        'project_y','carbrandid_y', 'id_y','shoppingmallid_y', 'status']]\n",
    "# resAllmer3_close1.columns = ['brand', 'guanwangid','city_name', 'name', 'type', 'gd_lng','gd_lat', 'address',  'project','carbrandid', \n",
    "#                              'id','shoppingmallid', 'status']\n",
    "\n",
    "# resAllmer_nwl = pd.concat([resAllmer2_,resAllmer3_opennew,resAllmer3_openyy,resAllmer3_close1],axis=0)\n",
    "\n",
    "# print('替换2后门店数量：%s'%resAllmer_nwl.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "8dee67f0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "蔚来门店数量是：944 暂停营业    362\n",
      "新店      360\n",
      "营业中     222\n",
      "Name: status, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "### 蔚来门店\n",
    "wlcurrent= xpcurrent_[xpcurrent_.brand == '蔚来']\n",
    "wllast = xplast_[xplast_.brand == '蔚来'] \n",
    "\n",
    "wlcurrent['name'] = wlcurrent['name'].map(lambda x: re.sub('蔚来中心','',str(x)))\n",
    "wlcurrent['name'] = wlcurrent['name'].map(lambda x: re.sub('蔚来空间','',str(x)))\n",
    "wllast['name'] = wllast['name'].map(lambda x: re.sub('蔚来中心','',str(x)))\n",
    "wllast['name'] = wllast['name'].map(lambda x: re.sub('蔚来空间','',str(x)))\n",
    "\n",
    "# wlcurrent = wlcurrent[~((wlcurrent.name.isin(['台州中盛广场','台州经开万达广场', '台州永宁','温岭银泰城'])) &(wlcurrent.city_name=='泰州市'))]\n",
    "# wlcurrent= wlcurrent[~((wlcurrent.name.isin(['泰州万象城','兴化吾悦广场', '靖江印象城'])) &(wlcurrent.city_name=='台州市'))]\n",
    "\n",
    "\n",
    "\n",
    "# 核查关联门店的匹配的条件（官网id、品牌名和类型）是否唯一\n",
    "cf1 = []\n",
    "cf2 = []\n",
    "if wlcurrent[wlcurrent.duplicated(subset=[\"name\",\"address\",\"type_new\"], keep=False)].shape[0]>0:\n",
    "    cf1 = wlcurrent[wlcurrent.duplicated(subset=[\"name\",\"address\",\"type_new\"], keep=False)]\n",
    "    print('本月匹配条件不唯一error!!!!!!!!!!!!!! cf1 !!!!!!!!!!!!!!')\n",
    "    \n",
    "if wllast[wllast.duplicated(subset=[\"name\",\"address\",\"type\"], keep=False)].shape[0]>0:\n",
    "    cf2 = wllast[wllast.duplicated(subset=[\"name\",\"address\",\"type\"], keep=False)]\n",
    "    print('上月匹配条件不唯一error!!!!!!!!!!!!!! !!!!cf2!!!!!!!!!!')\n",
    "    \n",
    "# 开始对比\n",
    "# guanwangid\n",
    "resAllmer1 = pd.merge(wlcurrent,wllast,left_on=[\"name\",\"address\",\"type_new\"],right_on=[\"name\",\"address\",\"type\"],how='outer')\n",
    "resAllmer1 = resAllmer1.reset_index(drop=True)\n",
    "resAllmer1['status']='营业中' \n",
    "resAllmer1.loc[resAllmer1[resAllmer1['brand_x'].isnull()].index,'status']='暂停营业'\n",
    "resAllmer1.loc[resAllmer1[resAllmer1['brand_y'].isnull()].index,'status']='新店'\n",
    "resAllopennew1 =resAllmer1[resAllmer1.status.isin(['新店'])]\n",
    "\n",
    "resAllopennew1 = resAllopennew1[['id_x', 'brand_x', 'guanwangid_x', 'address', 'city_name_x','is_open_x', 'gd_lng_x', 'gd_lat_x',  \n",
    "                                 'name_old', 'type_new','name', 'guanwangid_old1', 'guanwangid_old2', 'type',  'project', 'carbrandid', \n",
    "                                 'shoppingmallid','status']]\n",
    "\n",
    "resAllopennew1.columns = ['id', 'brand', 'guanwangid', 'address', 'city_name','is_open', 'gd_lng', 'gd_lat',\n",
    "                          'name_old', 'type_new','name', 'guanwangid_old1', 'guanwangid_old2', 'type',  'project', 'carbrandid',\n",
    "                          'shoppingmallid','status']\n",
    "\n",
    "resAllopennew1yy =resAllmer1[resAllmer1.status.isin(['营业中'])]\n",
    "\n",
    "resAllopennew1yy = resAllopennew1yy[['id_x', 'brand_x', 'guanwangid_x', 'address', 'city_name_x','is_open_x', 'gd_lng_y', 'gd_lat_y',  \n",
    "                                 'name_old', 'type_new','name', 'guanwangid_old1', 'guanwangid_old2', 'type',  'project', 'carbrandid', \n",
    "                                 'shoppingmallid','status']]\n",
    "\n",
    "resAllopennew1yy.columns = ['id', 'brand', 'guanwangid', 'address', 'city_name','is_open', 'gd_lng', 'gd_lat',\n",
    "                          'name_old', 'type_new','name', 'guanwangid_old1', 'guanwangid_old2', 'type',  'project', 'carbrandid',\n",
    "                          'shoppingmallid','status']\n",
    "\n",
    "\n",
    "resAllclose1 = resAllmer1[resAllmer1.status == '暂停营业']\n",
    "\n",
    "resAllclose1 = resAllclose1[['address', 'name','city_name_y', 'brand_y','type', 'gd_lng_y', 'gd_lat_y', 'project', 'carbrandid', \n",
    "                             'id_y', 'guanwangid_y','status']]  \n",
    "\n",
    "resAllclose1.columns = ['address', 'name','city_name', 'brand','type', 'gd_lng', 'gd_lat', 'project', 'carbrandid', \n",
    "                        'id', 'guanwangid','status']\n",
    "\n",
    "resAllmer_wl = pd.concat([resAllopennew1,resAllopennew1yy,resAllclose1])\n",
    "\n",
    "print('蔚来门店数量是：%s'%resAllmer_wl.shape[0],resAllmer_wl.status.value_counts())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8fe0f0b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "营业中+新店=本月店铺数量\n",
    "营业中+暂停=上月数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "ee815933",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "对比合并后门店数量：10154\n",
      "门店对比失败\n"
     ]
    }
   ],
   "source": [
    "### 将比对结果合并\n",
    "resAllmer = pd.concat([resAllmer_nwl,resAllmer_wl])\n",
    "print('对比合并后门店数量：%s'%resAllmer.shape[0])\n",
    "\n",
    "# 数据库没有问界和第三方售后，所以提出后对比结果\n",
    "opennum = resAllmer[resAllmer.status=='营业中']\n",
    "newnum = resAllmer[(resAllmer.status=='新店')&((resAllmer.brand!='问界')|(resAllmer.type_new!='第三方售后'))]\n",
    "closenum = resAllmer[resAllmer.status=='暂停营业']\n",
    "\n",
    "if (opennum.shape[0] + newnum.shape[0] == xpcurrent_.shape[0]) & (opennum.shape[0] + closenum.shape[0] == xplast_.shape[0]):\n",
    "    print('营业中%s;'%opennum.shape[0],'新店%s;'%newnum.shape[0],'暂停营业%s'%closenum.shape[0])\n",
    "else:\n",
    "    print('门店对比失败') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "4d7e1d0f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8964"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opennum.shape[0] + newnum.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "1fcf7949",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8952"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xpcurrent_.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "42f12b33",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7041"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opennum.shape[0] + closenum.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "2babcf93",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7041"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xplast_.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "6a045ee9",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>brand</th>\n",
       "      <th>guanwangid</th>\n",
       "      <th>address</th>\n",
       "      <th>city_name</th>\n",
       "      <th>is_open</th>\n",
       "      <th>gd_lng</th>\n",
       "      <th>gd_lat</th>\n",
       "      <th>name_old</th>\n",
       "      <th>type_new</th>\n",
       "      <th>name</th>\n",
       "      <th>name_last</th>\n",
       "      <th>type</th>\n",
       "      <th>project</th>\n",
       "      <th>guanwangid_old1</th>\n",
       "      <th>guanwangid_old2</th>\n",
       "      <th>carbrandid</th>\n",
       "      <th>shoppingmallid</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>62049.0</td>\n",
       "      <td>上汽R</td>\n",
       "      <td>18682.0</td>\n",
       "      <td>北京市房山区阎村镇炒米店村北京周路南侧80米</td>\n",
       "      <td>北京市</td>\n",
       "      <td>营业中</td>\n",
       "      <td>116.101647</td>\n",
       "      <td>39.719962</td>\n",
       "      <td>飞凡汽车北京房山特约服务站</td>\n",
       "      <td>4s店</td>\n",
       "      <td>飞凡汽车北京房山特约服务站|飞凡汽车北京房山体验中心</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>62667.0</td>\n",
       "      <td>问界</td>\n",
       "      <td>SCN227789</td>\n",
       "      <td>新疆伊犁伊宁市万容广场1F</td>\n",
       "      <td>伊犁哈萨克自治州</td>\n",
       "      <td>营业中</td>\n",
       "      <td>81.263790</td>\n",
       "      <td>43.931789</td>\n",
       "      <td>华为智能生活馆伊宁万容广场</td>\n",
       "      <td>体验店</td>\n",
       "      <td>华为智能生活馆伊宁万容广场</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>62317.0</td>\n",
       "      <td>问界</td>\n",
       "      <td>CNSCN262224</td>\n",
       "      <td>重庆市渝北区金渝大道99号附6号</td>\n",
       "      <td>重庆市</td>\n",
       "      <td>营业中</td>\n",
       "      <td>106.569466</td>\n",
       "      <td>29.644749</td>\n",
       "      <td>AITO授权用户中心重庆汽博中心</td>\n",
       "      <td>4s店</td>\n",
       "      <td>AITO授权用户中心重庆汽博中心</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>66246.0</td>\n",
       "      <td>理想</td>\n",
       "      <td>MBURC03</td>\n",
       "      <td>新疆乌鲁木齐经济技术开发区喀什西路 600号</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>营业中</td>\n",
       "      <td>87.533008</td>\n",
       "      <td>43.884145</td>\n",
       "      <td>理想汽车乌鲁木齐经开服务中心喀什西路临时店</td>\n",
       "      <td>第三方售后|临时</td>\n",
       "      <td>理想汽车乌鲁木齐经开服务中心喀什西路临时店</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288</th>\n",
       "      <td>63224.0</td>\n",
       "      <td>问界</td>\n",
       "      <td>SCN173607</td>\n",
       "      <td>新疆乌鲁木齐市天山区光明路39号ccmall时代广场</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>营业中</td>\n",
       "      <td>87.605798</td>\n",
       "      <td>43.802426</td>\n",
       "      <td>华为智能生活馆乌鲁木齐CCMALL</td>\n",
       "      <td>体验店</td>\n",
       "      <td>华为智能生活馆乌鲁木齐CCMALL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577</th>\n",
       "      <td>593.0</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...</td>\n",
       "      <td>四川省乐山市清江西区蟠龙路世豪广场C103/C103A</td>\n",
       "      <td>乐山市</td>\n",
       "      <td>营业中</td>\n",
       "      <td>103.726581</td>\n",
       "      <td>29.602213</td>\n",
       "      <td>蔚来空间乐山世豪广场</td>\n",
       "      <td>体验店</td>\n",
       "      <td>乐山世豪广场</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>578</th>\n",
       "      <td>594.0</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...</td>\n",
       "      <td>四川省泸州市江阳区康城路一段1号LG231</td>\n",
       "      <td>泸州市</td>\n",
       "      <td>营业中</td>\n",
       "      <td>105.409866</td>\n",
       "      <td>28.893980</td>\n",
       "      <td>蔚来空间泸州万象汇</td>\n",
       "      <td>体验店</td>\n",
       "      <td>泸州万象汇</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>579</th>\n",
       "      <td>595.0</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...</td>\n",
       "      <td>山东省日照市东港区烟台路176号L119-2号铺</td>\n",
       "      <td>日照市</td>\n",
       "      <td>营业中</td>\n",
       "      <td>119.532553</td>\n",
       "      <td>35.420486</td>\n",
       "      <td>蔚来空间日照万象汇</td>\n",
       "      <td>体验店</td>\n",
       "      <td>日照万象汇</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580</th>\n",
       "      <td>596.0</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...</td>\n",
       "      <td>河北省邯郸市丛台区东环路北路东联华汽车广场园内</td>\n",
       "      <td>邯郸市</td>\n",
       "      <td>营业中</td>\n",
       "      <td>114.536938</td>\n",
       "      <td>36.643505</td>\n",
       "      <td>蔚来空间邯郸联华</td>\n",
       "      <td>体验店</td>\n",
       "      <td>邯郸联华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>581</th>\n",
       "      <td>597.0</td>\n",
       "      <td>蔚来</td>\n",
       "      <td>https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...</td>\n",
       "      <td>安徽省芜湖市镜湖区赭山街道中山北路33号芜湖八佰伴6023L-F1002铺位</td>\n",
       "      <td>芜湖市</td>\n",
       "      <td>营业中</td>\n",
       "      <td>118.366425</td>\n",
       "      <td>31.339603</td>\n",
       "      <td>蔚来中心芜湖滨江</td>\n",
       "      <td>体验店</td>\n",
       "      <td>芜湖滨江</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新店</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3113 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id brand                                         guanwangid  \\\n",
       "19   62049.0   上汽R                                            18682.0   \n",
       "267  62667.0    问界                                          SCN227789   \n",
       "278  62317.0    问界                                        CNSCN262224   \n",
       "280  66246.0    理想                                            MBURC03   \n",
       "288  63224.0    问界                                          SCN173607   \n",
       "..       ...   ...                                                ...   \n",
       "577    593.0    蔚来  https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...   \n",
       "578    594.0    蔚来  https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...   \n",
       "579    595.0    蔚来  https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...   \n",
       "580    596.0    蔚来  https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...   \n",
       "581    597.0    蔚来  https://www.nio.cn/cdn-static/mynio/cms/zh_CN/...   \n",
       "\n",
       "                                    address city_name is_open      gd_lng  \\\n",
       "19                   北京市房山区阎村镇炒米店村北京周路南侧80米       北京市     营业中  116.101647   \n",
       "267                           新疆伊犁伊宁市万容广场1F  伊犁哈萨克自治州     营业中   81.263790   \n",
       "278                        重庆市渝北区金渝大道99号附6号       重庆市     营业中  106.569466   \n",
       "280                  新疆乌鲁木齐经济技术开发区喀什西路 600号     乌鲁木齐市     营业中   87.533008   \n",
       "288              新疆乌鲁木齐市天山区光明路39号ccmall时代广场     乌鲁木齐市     营业中   87.605798   \n",
       "..                                      ...       ...     ...         ...   \n",
       "577             四川省乐山市清江西区蟠龙路世豪广场C103/C103A       乐山市     营业中  103.726581   \n",
       "578                   四川省泸州市江阳区康城路一段1号LG231       泸州市     营业中  105.409866   \n",
       "579                山东省日照市东港区烟台路176号L119-2号铺       日照市     营业中  119.532553   \n",
       "580                 河北省邯郸市丛台区东环路北路东联华汽车广场园内       邯郸市     营业中  114.536938   \n",
       "581  安徽省芜湖市镜湖区赭山街道中山北路33号芜湖八佰伴6023L-F1002铺位       芜湖市     营业中  118.366425   \n",
       "\n",
       "        gd_lat               name_old  type_new                        name  \\\n",
       "19   39.719962          飞凡汽车北京房山特约服务站       4s店  飞凡汽车北京房山特约服务站|飞凡汽车北京房山体验中心   \n",
       "267  43.931789          华为智能生活馆伊宁万容广场       体验店               华为智能生活馆伊宁万容广场   \n",
       "278  29.644749       AITO授权用户中心重庆汽博中心       4s店            AITO授权用户中心重庆汽博中心   \n",
       "280  43.884145  理想汽车乌鲁木齐经开服务中心喀什西路临时店  第三方售后|临时       理想汽车乌鲁木齐经开服务中心喀什西路临时店   \n",
       "288  43.802426      华为智能生活馆乌鲁木齐CCMALL       体验店           华为智能生活馆乌鲁木齐CCMALL   \n",
       "..         ...                    ...       ...                         ...   \n",
       "577  29.602213             蔚来空间乐山世豪广场       体验店                      乐山世豪广场   \n",
       "578  28.893980              蔚来空间泸州万象汇       体验店                       泸州万象汇   \n",
       "579  35.420486              蔚来空间日照万象汇       体验店                       日照万象汇   \n",
       "580  36.643505               蔚来空间邯郸联华       体验店                        邯郸联华   \n",
       "581  31.339603               蔚来中心芜湖滨江       体验店                        芜湖滨江   \n",
       "\n",
       "    name_last type project guanwangid_old1 guanwangid_old2  carbrandid  \\\n",
       "19        NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "267       NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "278       NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "280       NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "288       NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "..        ...  ...     ...             ...             ...         ...   \n",
       "577       NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "578       NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "579       NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "580       NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "581       NaN  NaN     NaN             NaN             NaN         NaN   \n",
       "\n",
       "     shoppingmallid status  \n",
       "19              NaN     新店  \n",
       "267             NaN     新店  \n",
       "278             NaN     新店  \n",
       "280             NaN     新店  \n",
       "288             NaN     新店  \n",
       "..              ...    ...  \n",
       "577             NaN     新店  \n",
       "578             NaN     新店  \n",
       "579             NaN     新店  \n",
       "580             NaN     新店  \n",
       "581             NaN     新店  \n",
       "\n",
       "[3113 rows x 19 columns]"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newnum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "5e7395a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "resAllmer.to_excel(r'C:\\Users\\NewUser\\Desktop\\本月test2.xlsx')\n",
    "xpcurrent_.to_excel(r'C:\\Users\\NewUser\\Desktop\\上月test3.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "95ff32fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 输出合并的结果表\n",
    "resAllmer.to_excel(r'C:\\Users\\NewUser\\Desktop\\门店对比结果表0614_3.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "93b2b045",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9606, 20)"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  人工清洗流程\n",
    "# 1 新店和暂停营业的店铺是否存在未匹配上的  \n",
    "# 2 新店是否存在重复\n",
    "# 3 营业中的店铺本月和上月命名是否存在较大偏差——官网命名出现大批量改动，就需要人工处理了（type_new等）\n",
    "# 4 检查经纬度重复的门店\n",
    "# 5 检查type_new字段是否存在缺失值\n",
    "\n",
    "# 导入新清洗的结果表\n",
    "src = r'C:\\Users\\NewUser\\Desktop\\门店对比结果表0614_2.xlsx'\n",
    "xpmer = pd.read_excel(src)\n",
    "xpmer.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "a4e6825a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['体验店', '4s店', '服务中心', '4s店|交付中心', '服务中心|交付中心', '第三方售后', '服务中心|临时',\n",
       "       '体验店|交付中心', '交付中心', '卫星店', '体验店|交付中心|临时', '4s店|交付中心|展厅', '交付中心|展厅'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xpmer.type_new.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "7e2aec31",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1035, 21)"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "showtypeDict = {\n",
    "    1:['体验店','体验店|交付中心'],\n",
    "    2:['4s店','4s店|交付中心'],\n",
    "    3:['服务中心','服务中心|交付中心'],\n",
    "    4:['交付中心'],\n",
    "}\n",
    "for key,value in showtypeDict.items():\n",
    "    xpmer.loc[xpmer[xpmer.type_new.isin(value)].index,'showtype']=key\n",
    "\n",
    "# 新门店最大的carbrandid  ads_city_car库showtype<=4的最大id\n",
    "Tmax = 10714\n",
    "xpmernew = xpmer[(xpmer['carbrandid'].isnull())\n",
    "                 &(xpmer.type_new.isin(['体验店','体验店|交付中心','4s店','4s店|交付中心','服务中心', '服务中心|交付中心','交付中心',\n",
    "                                        '服务中心|交付中心|卫星店','卫星店','体验店|卫星店']))\n",
    "                 &(xpmer.brand!='问界')]\n",
    "xpmernew.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "b9701bdf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "carbrandid更新成功10078;\n",
      "交付中心更新的数量472;\n"
     ]
    }
   ],
   "source": [
    "xpmernew.loc[:,'carbrandid'] = range(1,len(xpmernew)+1)\n",
    "xpmernew.loc[:,'carbrandid'] = xpmernew['carbrandid']+Tmax\n",
    "xpoter = xpmer[~xpmer.id.isin(xpmernew.id)]\n",
    "# 将新店的carbrandid合并到总表中\n",
    "xpall = pd.concat([xpmernew,xpoter])\n",
    "# 把复合型的门店且营业中的店铺提炼出来\n",
    "xpadd = xpall[(xpall.type_new=='体验店|交付中心')\n",
    "              |(xpall.type_new=='4s店|交付中心')\n",
    "              |(xpall.type_new=='服务中心|交付中心')\n",
    "              |(xpall.type_new=='展厅|交付中心')\n",
    "              |(xpall.type_new=='服务中心|交付中心|卫星店')\n",
    "             ]\n",
    "xpadd['type_new']='交付中心'\n",
    "xpadd['showtype']=5\n",
    "xpadd['carbrandid']=xpadd['carbrandid'].astype(int)\n",
    "xpadd['carbrandid'] = xpadd['carbrandid']+1000000\n",
    "# 表合并，生成结果表\n",
    "xpnewmonth = pd.concat([xpall,xpadd])\n",
    "\n",
    "# 更改参数\n",
    "xpnewmonth['update']='20230601'\n",
    "xpnewmonth['name_formatted']=xpnewmonth['name']\n",
    "xpnewmonth.to_excel(r'C:\\Users\\NewUser\\Desktop\\本月结果表.xlsx')\n",
    "\n",
    "\n",
    "if xpmer.shape[0] + xpadd.shape[0] == xpnewmonth.shape[0]:\n",
    "    print('carbrandid更新成功%s;'%xpnewmonth.shape[0])\n",
    "else:\n",
    "    print('carbrandid更新失败') \n",
    "\n",
    "print('交付中心更新的数量%s;'%xpadd.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "437b6d2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8491, 22)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xpnewmonth.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "22f5b55b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "本月理想的第三方售后553 上个月理想的第三方售后548\n",
      "理想第三方售后门店558;\n"
     ]
    }
   ],
   "source": [
    "# 将本月理想的第三方售后 和上个月进行对比\n",
    "lx = '''\n",
    "select *\n",
    "from ads_city_car_finall\n",
    "where update='20230501'\n",
    "and type_new='第三方售后'\n",
    "'''\n",
    "lx_last = myconnNormalenv.fetchAll(lx)\n",
    "lx_currrent = xpnewmonth[xpnewmonth.type_new=='第三方售后']\n",
    "print('本月理想的第三方售后%s'%lx_currrent.shape[0],'上个月理想的第三方售后%s'%lx_last.shape[0])\n",
    "\n",
    "# 本月和上月进行对比\n",
    "lx = pd.merge(lx_currrent,lx_last,on=['guanwangid'],how='outer')\n",
    "lx = lx.reset_index(drop=True)\n",
    "lx['status']='营业中' \n",
    "lx.loc[lx[lx['name_x'].isnull()].index,'status']='暂停营业'\n",
    "lx.loc[lx[lx['name_y'].isnull()].index,'status']='新店'\n",
    "lx_new =lx[lx.status.isin(['新店'])]\n",
    "lx_new = lx_new[['id_x', 'brand_x', 'guanwangid', 'address_x', 'city_name_x', 'is_open','name_old_x', 'type_new_x', 'name_x', 'name_last_x',\n",
    "                 'gd_lng_x','gd_lat_x', 'type_x','guanwangid_old1_x', 'guanwangid_old2_x', 'showtype_x','update_x','status']]\n",
    "lx_new.columns = ['id', 'brand', 'guanwangid', 'address', 'city_name', 'is_open','name_old', 'type_new', 'name', 'name_last',\n",
    "                 'gd_lng','gd_lat', 'type','guanwangid_old1', 'guanwangid_old2', 'showtype','update','status']\n",
    "\n",
    "lx_open =lx[lx.status.isin(['营业中'])]\n",
    "lx_open = lx_open[['id_x', 'brand_x', 'guanwangid', 'address_x', 'city_name_x', 'is_open','type_new_x', 'name_x', 'name_y',\n",
    "                   'gd_lng_y','gd_lat_y', 'type_x', 'project_y', 'carbrandid_y', 'shoppingmallid_y','guanwangid_old1_x', 'guanwangid_old2_x',\n",
    "                    'update_x',  'status']]\n",
    "lx_open.columns = ['id', 'brand', 'guanwangid', 'address', 'city_name', 'is_open','type_new', 'name', 'name_last',\n",
    "                   'gd_lng','gd_lat', 'type', 'project', 'carbrandid', 'shoppingmallid','guanwangid_old1', 'guanwangid_old2',\n",
    "                   'update', 'status']\n",
    "\n",
    "lx_close =lx[lx.status.isin(['暂停营业'])]\n",
    "lx_close = lx_close[[ 'id_y', 'brand_y', 'address_y','city_name_y', 'gd_lng_y', 'gd_lat_y', 'name_old_y', 'type_new_y','name_y',\n",
    "                     'guanwangid_old1_y', 'guanwangid_old2_y', 'name_last_y','type_y', 'project_y', 'carbrandid_y', 'shoppingmallid_y',\n",
    "                     'status']]\n",
    "lx_close.columns = [ 'id', 'brand', 'address','city_name', 'gd_lng', 'gd_lat', 'name_old', 'type_new','name',\n",
    "                     'guanwangid_old1', 'guanwangid_old2', 'name_last','type', 'project', 'carbrandid', 'shoppingmallid','status']\n",
    "\n",
    "lxall = pd.concat([lx_new,lx_open,lx_close],0)\n",
    "\n",
    "\n",
    "if (lx_currrent.shape[0] + lx_close.shape[0] == lxall.shape[0])&(lx_last.shape[0] + lx_new.shape[0] == lxall.shape[0]):\n",
    "    print('理想第三方售后门店%s;'%lxall.shape[0])\n",
    "else:\n",
    "    print('失败') \n",
    "    \n",
    "# lxall[lxall.status!='营业中'].city_name.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "e5968ada",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "本月问界975 上个月问界1111\n",
      "问界门店1148;\n"
     ]
    }
   ],
   "source": [
    "# 将本月问界 和上个月进行对比\n",
    "wj = '''\n",
    "select *\n",
    "from ads_city_car_finall\n",
    "where update='20230501'\n",
    "and brand='问界'\n",
    "'''\n",
    "wj_last = myconnNormalenv.fetchAll(wj)\n",
    "wj_currrent = xpnewmonth[xpnewmonth.brand=='问界']\n",
    "print('本月问界%s'%wj_currrent.shape[0],'上个月问界%s'%wj_last.shape[0])\n",
    "\n",
    "# 本月和上月进行对比\n",
    "wj = pd.merge(wj_currrent,wj_last,on=['guanwangid'],how='outer')\n",
    "wj = wj.reset_index(drop=True)\n",
    "wj['status']='营业中' \n",
    "wj.loc[wj[wj['name_x'].isnull()].index,'status']='暂停营业'\n",
    "wj.loc[wj[wj['name_y'].isnull()].index,'status']='新店'\n",
    "wj_new =wj[wj.status.isin(['新店'])]\n",
    "wj_new = wj_new[['id_x', 'brand_x', 'guanwangid', 'address_x', 'city_name_x', 'name_old_x', 'type_new_x', 'name_x', 'name_last_x',\n",
    "                 'gd_lng_x','gd_lat_x', 'type_x','guanwangid_old1_x', 'guanwangid_old2_x', 'showtype_x','update_x','status']]\n",
    "wj_new.columns = ['id', 'brand', 'guanwangid', 'address', 'city_name', 'name_old', 'type_new', 'name', 'name_last',\n",
    "                 'gd_lng','gd_lat', 'type','guanwangid_old1', 'guanwangid_old2', 'showtype','update','status']\n",
    "\n",
    "wj_open =wj[wj.status.isin(['营业中'])]\n",
    "wj_open = wj_open[['id_x', 'brand_x', 'guanwangid', 'address_x', 'city_name_x', 'type_new_x', 'name_x', 'name_y',\n",
    "                   'gd_lng_y','gd_lat_y', 'type_x', 'project_y', 'carbrandid_y', 'shoppingmallid_y','guanwangid_old1_x', 'guanwangid_old2_x',\n",
    "                    'update_x',  'status']]\n",
    "wj_open.columns = ['id', 'brand', 'guanwangid', 'address', 'city_name', 'type_new', 'name', 'name_last',\n",
    "                   'gd_lng','gd_lat', 'type', 'project', 'carbrandid', 'shoppingmallid','guanwangid_old1', 'guanwangid_old2',\n",
    "                   'update', 'status']\n",
    "\n",
    "wj_close =wj[wj.status.isin(['暂停营业'])]\n",
    "wj_close = wj_close[[ 'id_y', 'brand_y', 'address_y','city_name_y', 'gd_lng_y', 'gd_lat_y', 'name_old_y', 'type_new_y','name_y',\n",
    "                     'guanwangid_old1_y', 'guanwangid_old2_y', 'name_last_y','type_y', 'project_y', 'carbrandid_y', 'shoppingmallid_y',\n",
    "                     'status']]\n",
    "wj_close.columns = [ 'id', 'brand', 'address','city_name', 'gd_lng', 'gd_lat', 'name_old', 'type_new','name',\n",
    "                     'guanwangid_old1', 'guanwangid_old2', 'name_last','type', 'project', 'carbrandid', 'shoppingmallid','status']\n",
    "\n",
    "wjall = pd.concat([wj_new,wj_open,wj_close],0)\n",
    "\n",
    "if (wj_last.shape[0] + wj_new.shape[0] == wjall.shape[0])&(wj_currrent.shape[0] + wj_close.shape[0] == wjall.shape[0]):\n",
    "    print('问界门店%s;'%wjall.shape[0])\n",
    "else:\n",
    "    print('失败') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4516341a",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'C:\\\\Users\\\\NewUser\\\\Desktop\\\\问界门店.xlsx'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_23196\\3602433969.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 导入新清洗的结果表\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[0msrc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34mr'C:\\Users\\NewUser\\Desktop\\问界门店.xlsx'\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[0mwjall\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_excel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msrc\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      4\u001b[0m \u001b[0mwjall\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\anaconda\\lib\\site-packages\\pandas\\util\\_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    309\u001b[0m                     \u001b[0mstacklevel\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstacklevel\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    310\u001b[0m                 )\n\u001b[1;32m--> 311\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    312\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    313\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\anaconda\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36mread_excel\u001b[1;34m(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, decimal, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)\u001b[0m\n\u001b[0;32m    455\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    456\u001b[0m         \u001b[0mshould_close\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 457\u001b[1;33m         \u001b[0mio\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstorage_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstorage_options\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    458\u001b[0m     \u001b[1;32melif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m!=\u001b[0m \u001b[0mio\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    459\u001b[0m         raise ValueError(\n",
      "\u001b[1;32mD:\\anaconda\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, path_or_buffer, engine, storage_options)\u001b[0m\n\u001b[0;32m   1374\u001b[0m                 \u001b[0mext\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"xls\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1375\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1376\u001b[1;33m                 ext = inspect_excel_format(\n\u001b[0m\u001b[0;32m   1377\u001b[0m                     \u001b[0mcontent_or_path\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstorage_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstorage_options\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1378\u001b[0m                 )\n",
      "\u001b[1;32mD:\\anaconda\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36minspect_excel_format\u001b[1;34m(content_or_path, storage_options)\u001b[0m\n\u001b[0;32m   1248\u001b[0m         \u001b[0mcontent_or_path\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mBytesIO\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcontent_or_path\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1249\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1250\u001b[1;33m     with get_handle(\n\u001b[0m\u001b[0;32m   1251\u001b[0m         \u001b[0mcontent_or_path\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"rb\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstorage_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstorage_options\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mis_text\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1252\u001b[0m     ) as handle:\n",
      "\u001b[1;32mD:\\anaconda\\lib\\site-packages\\pandas\\io\\common.py\u001b[0m in \u001b[0;36mget_handle\u001b[1;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001b[0m\n\u001b[0;32m    793\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    794\u001b[0m             \u001b[1;31m# Binary mode\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 795\u001b[1;33m             \u001b[0mhandle\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mhandle\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mioargs\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmode\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    796\u001b[0m         \u001b[0mhandles\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mhandle\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    797\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'C:\\\\Users\\\\NewUser\\\\Desktop\\\\问界门店.xlsx'"
     ]
    }
   ],
   "source": [
    "# 导入新清洗的结果表\n",
    "src = r'C:\\Users\\NewUser\\Desktop\\门店类型合并结果表.xlsx'\n",
    "wjall = pd.read_excel(src)\n",
    "wjall.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "0e2ae73e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "合并后门店10256;\n"
     ]
    }
   ],
   "source": [
    "# 将小鹏数据和（问界和第三方售后）合并\n",
    "xpnewmonth_ = xpnewmonth[(xpnewmonth.brand!='问界')&(xpnewmonth.type_new!='第三方售后')]\n",
    "\n",
    "xpnewmonthall = pd.concat([xpnewmonth_,wjall,lxall],0)\n",
    "\n",
    "if xpnewmonth.shape[0] + wj_close.shape[0]+ lx_close.shape[0] == xpnewmonthall.shape[0]:\n",
    "    print('合并后门店%s;'%xpnewmonthall.shape[0])\n",
    "else:\n",
    "    print('失败') \n",
    "\n",
    "xpnewmonthall.to_excel(r'C:\\Users\\NewUser\\Desktop\\本月结果表0614_1.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "72e8b96c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10256, 24)"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  人工清洗流程\n",
    "# 1 检查理想第三方售后和问界的新店和暂停营业是否存在重复\n",
    "# 2 新店是否存在重复\n",
    "# 3 营业中的店铺本月和上月命名是否存在较大偏差\n",
    "# 4 检查经纬度重复的门店\n",
    "# 5 检查type_new字段是否存在缺失值\n",
    "\n",
    "# 导入新清洗的结果表\n",
    "src = r'C:\\Users\\NewUser\\Desktop\\本月结果表0614_1.xlsx'\n",
    "xpnewmonthall = pd.read_excel(src)\n",
    "xpnewmonthall['update']='20230614'\n",
    "xpnewmonthall.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "819d68d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "xpnewmonthall = xpnewmonthall[['id', 'brand', 'guanwangid', 'address',\n",
    "       'city_name', 'gd_lng', 'gd_lat', 'name_old', 'type_new',\n",
    "       'name', 'name_last', 'type', 'project', 'guanwangid_old1',\n",
    "       'guanwangid_old2', 'carbrandid', 'shoppingmallid',\n",
    "       'status', 'showtype', 'update', 'name_formatted']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "834c3bdf",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0.1', 'Unnamed: 0', 'id', 'brand', 'guanwangid', 'address',\n",
       "       'city_name', 'is_open', 'gd_lng', 'gd_lat', 'name_old', 'type_new',\n",
       "       'name', 'name_last', 'type', 'project', 'guanwangid_old1',\n",
       "       'guanwangid_old2', 'carbrandid', 'shoppingmallid', 'status', 'showtype',\n",
       "       'update', 'name_formatted'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xpnewmonthall.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "b8be1524",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入到数据库中 ads_city_car_finall\n",
    "myconnNormalenv.dataToSql(xpnewmonthall,'ads_city_car_finall')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "d3dfb090",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 待开业、展厅等数据单独处理\n",
    "xpcurrentsoon = xpcurrentsoon[['id', 'brand', 'guanwangid','address','gd_lng', 'gd_lat', 'name',  'is_open','type_new', 'city_name']]\n",
    "xpcurrentsoon.columns = ['id', 'brand', 'guanwangid','address','gd_lng', 'gd_lat', 'name',  'status','type_new', 'city_name']\n",
    "xpcurrentsoon['update']='20230614'\n",
    "# xpcurrentsoon.to_excel(r'C:\\Users\\NewUser\\Desktop\\本月结果表202306.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "362fb9b8",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>brand</th>\n",
       "      <th>guanwangid</th>\n",
       "      <th>address</th>\n",
       "      <th>gd_lng</th>\n",
       "      <th>gd_lat</th>\n",
       "      <th>name</th>\n",
       "      <th>status</th>\n",
       "      <th>type_new</th>\n",
       "      <th>city_name</th>\n",
       "      <th>update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>61632</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>18333</td>\n",
       "      <td>新疆喀什地区喀什市东湖街道世纪大道社区世纪大道J7号楼1层1002号商铺</td>\n",
       "      <td>76.025055</td>\n",
       "      <td>39.468845</td>\n",
       "      <td>比亚迪汽车海洋网喀什世纪大道绿迪城市展厅</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>喀什地区</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>60307</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>18331</td>\n",
       "      <td>新疆博州博乐市南城区街道恒大国际综合汽车生活广场14号楼1层1号-5号</td>\n",
       "      <td>82.070000</td>\n",
       "      <td>44.850000</td>\n",
       "      <td>比亚迪汽车王朝网秦汉恒大汽车城城市展厅</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>博尔塔拉蒙古自治州</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>60317</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>15695</td>\n",
       "      <td>新疆塔城地区额敏县额铁路三十四地段865东1号</td>\n",
       "      <td>83.646940</td>\n",
       "      <td>46.513012</td>\n",
       "      <td>比亚迪汽车王朝网盛鸿额敏汽车城城市展厅</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>塔城地区</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>60314</td>\n",
       "      <td>比亚迪</td>\n",
       "      <td>17260</td>\n",
       "      <td>新疆奎屯市南环西路汽贸园</td>\n",
       "      <td>84.849140</td>\n",
       "      <td>44.411769</td>\n",
       "      <td>比亚迪汽车王朝网新疆领途比亚迪城市销售展厅</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>伊犁哈萨克自治州</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>62791</td>\n",
       "      <td>问界</td>\n",
       "      <td>CNSCN241296</td>\n",
       "      <td>新疆维吾尔族自治区库尔勒市朝阳路新汇嘉商场一楼</td>\n",
       "      <td>86.157063</td>\n",
       "      <td>41.742111</td>\n",
       "      <td>AITO体验展厅库尔勒新汇嘉</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>巴音郭楞蒙古自治州</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10576</th>\n",
       "      <td>66580</td>\n",
       "      <td>岚图</td>\n",
       "      <td>KTJ01</td>\n",
       "      <td>天津市和平区和平路263号天河城购物中心L1层</td>\n",
       "      <td>117.204195</td>\n",
       "      <td>39.127123</td>\n",
       "      <td>岚图展厅天津天河城店</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>天津市</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10578</th>\n",
       "      <td>66582</td>\n",
       "      <td>岚图</td>\n",
       "      <td>PTJ01</td>\n",
       "      <td>天津市河东区津滨大道160号1层中庭</td>\n",
       "      <td>117.262198</td>\n",
       "      <td>39.117734</td>\n",
       "      <td>岚图展厅天津河东爱情海购物公园店</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>天津市</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10586</th>\n",
       "      <td>66590</td>\n",
       "      <td>岚图</td>\n",
       "      <td>KBJ14</td>\n",
       "      <td>北京市大兴区荣京东街与宏达中路交叉口西南角北京大族广场一楼中庭</td>\n",
       "      <td>116.512863</td>\n",
       "      <td>39.791831</td>\n",
       "      <td>岚图展厅北京大族广场店</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>北京市</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10587</th>\n",
       "      <td>66591</td>\n",
       "      <td>岚图</td>\n",
       "      <td>KBJ11</td>\n",
       "      <td>北京市通州区新华西街58号通州万达广场1F-1号门中庭</td>\n",
       "      <td>116.642567</td>\n",
       "      <td>39.905275</td>\n",
       "      <td>岚图展厅北京通州万达店</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>北京市</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10591</th>\n",
       "      <td>66595</td>\n",
       "      <td>岚图</td>\n",
       "      <td>KBJ12</td>\n",
       "      <td>北京市大兴区欣宁街15号</td>\n",
       "      <td>116.326585</td>\n",
       "      <td>39.788024</td>\n",
       "      <td>岚图展厅北京荟聚购物中心店</td>\n",
       "      <td>营业中</td>\n",
       "      <td>展厅</td>\n",
       "      <td>北京市</td>\n",
       "      <td>20230614</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1204 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id brand   guanwangid                               address  \\\n",
       "2      61632   比亚迪        18333  新疆喀什地区喀什市东湖街道世纪大道社区世纪大道J7号楼1层1002号商铺   \n",
       "12     60307   比亚迪        18331   新疆博州博乐市南城区街道恒大国际综合汽车生活广场14号楼1层1号-5号   \n",
       "13     60317   比亚迪        15695               新疆塔城地区额敏县额铁路三十四地段865东1号   \n",
       "15     60314   比亚迪        17260                          新疆奎屯市南环西路汽贸园   \n",
       "16     62791    问界  CNSCN241296               新疆维吾尔族自治区库尔勒市朝阳路新汇嘉商场一楼   \n",
       "...      ...   ...          ...                                   ...   \n",
       "10576  66580    岚图        KTJ01               天津市和平区和平路263号天河城购物中心L1层   \n",
       "10578  66582    岚图        PTJ01                    天津市河东区津滨大道160号1层中庭   \n",
       "10586  66590    岚图        KBJ14       北京市大兴区荣京东街与宏达中路交叉口西南角北京大族广场一楼中庭   \n",
       "10587  66591    岚图        KBJ11           北京市通州区新华西街58号通州万达广场1F-1号门中庭   \n",
       "10591  66595    岚图        KBJ12                          北京市大兴区欣宁街15号   \n",
       "\n",
       "           gd_lng     gd_lat                   name status type_new  \\\n",
       "2       76.025055  39.468845   比亚迪汽车海洋网喀什世纪大道绿迪城市展厅    营业中       展厅   \n",
       "12      82.070000  44.850000    比亚迪汽车王朝网秦汉恒大汽车城城市展厅    营业中       展厅   \n",
       "13      83.646940  46.513012    比亚迪汽车王朝网盛鸿额敏汽车城城市展厅    营业中       展厅   \n",
       "15      84.849140  44.411769  比亚迪汽车王朝网新疆领途比亚迪城市销售展厅    营业中       展厅   \n",
       "16      86.157063  41.742111         AITO体验展厅库尔勒新汇嘉    营业中       展厅   \n",
       "...           ...        ...                    ...    ...      ...   \n",
       "10576  117.204195  39.127123             岚图展厅天津天河城店    营业中       展厅   \n",
       "10578  117.262198  39.117734       岚图展厅天津河东爱情海购物公园店    营业中       展厅   \n",
       "10586  116.512863  39.791831            岚图展厅北京大族广场店    营业中       展厅   \n",
       "10587  116.642567  39.905275            岚图展厅北京通州万达店    营业中       展厅   \n",
       "10591  116.326585  39.788024          岚图展厅北京荟聚购物中心店    营业中       展厅   \n",
       "\n",
       "       city_name    update  \n",
       "2           喀什地区  20230614  \n",
       "12     博尔塔拉蒙古自治州  20230614  \n",
       "13          塔城地区  20230614  \n",
       "15      伊犁哈萨克自治州  20230614  \n",
       "16     巴音郭楞蒙古自治州  20230614  \n",
       "...          ...       ...  \n",
       "10576        天津市  20230614  \n",
       "10578        天津市  20230614  \n",
       "10586        北京市  20230614  \n",
       "10587        北京市  20230614  \n",
       "10591        北京市  20230614  \n",
       "\n",
       "[1204 rows x 11 columns]"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xpcurrentsoon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "90e6daa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "xpcurrentsoon.to_excel(r'C:\\Users\\NewUser\\Desktop\\本月结果表0614_4.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "520f1957",
   "metadata": {},
   "outputs": [],
   "source": [
    "# xpnewmonthall = xpnewmonthall[['id', 'brand', 'guanwangid','address','gd_lng', 'gd_lat', 'name',  'is_open','type_new', 'city_name','update']]\n",
    "# xpnewmonthall.to_excel(r'C:\\Users\\NewUser\\Desktop\\本月结果表2023061.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "94c58748",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1204, 12)"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "src1 = r'C:\\Users\\NewUser\\Desktop\\本月结果表0614_4.xlsx'\n",
    "xpcurrentsoon = pd.read_excel(src1)\n",
    "xpcurrentsoon['update']='20230614'\n",
    "xpcurrentsoon.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "4d43f82a",
   "metadata": {},
   "outputs": [],
   "source": [
    "xpcurrentsoon = xpcurrentsoon[['id', 'brand', 'guanwangid', 'address', 'gd_lng',\n",
    "       'gd_lat', 'name', 'status', 'type_new', 'city_name', 'update']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "4c8a92a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'brand', 'guanwangid', 'address', 'gd_lng', 'gd_lat', 'name',\n",
       "       'status', 'type_new', 'city_name', 'update'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xpcurrentsoon.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "7eadc9bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入到数据库中 ads_city_car_finall\n",
    "myconnNormalenv.dataToSql(xpcurrentsoon,'ads_city_car_finall')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "781px",
    "left": "43px",
    "top": "112px",
    "width": "372.969px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
